I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you. Russell

Sumproduct sounds like it will help (tho I don't quite see what you're
trying to do with your chart.. not sure what goes in it).

=SUMPRODUCT((A1:A9002)*(A1:A900<4))

will count 'scores' in A1:A900 that start with 3.

When you say you want to "count the numbers only if.." - are you saying you
want to simply count how many numbers there are within a certain score
range? If so, you can use the formula above if each score always has a
number associated with it. If not, you can use something like

=SUMPRODUCT((A1:A9002)*(A1:A900<4)*(B1:B900<""))

where B1:B900 would house the number. This simply won't count scores that
don't have any number in B1:B900.

If you want to ADD the numbers corresponding to certain scores (e.g. add up
all the numbers that go with the score of 3), you can use smoething like;
=SUMPRODUCT((A1:A9002)*(A1:A900<4),B1:B900)

with B1:B900 containing the numbers you want added.


"Russell Hampton" <Russell wrote in
message ...
I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come
up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you.
Russell

I think an example might help.

tj

"Russell Hampton" wrote:

I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you. Russell

Hi TJ,

This is what I am working with. So for every 1 in the list I want it to add
if the score is in a range. So given the example, how many are there that are
less than 600? THe answer is one. And for 600-650 the answer is 5. Then I
will plug this into a chart like the one below. Does that help? I appreciate
any insight you may have.

Collateral Code Credit Score
1 586
1 605
1 633
1 640
1 643
1 643
1 651
1 656
1 659
1 665
1 692
1 696
1 699
1 701
1 702
1 704
1 716
1 724
1 754
1 773

Chart example:
Jan-04
A B C D
710 + 650-709 620-649 575-619
Count Count Count Count
Unsecured Consumer Loans
Unsecured Visa Classic
Unsecured Visa Gold
New Vehicle Direct
Used Vehicle Direct
New Vehicle Indirect
Used Vehicle Indirect
First Mortgage
Second Mortgage Fixed
Home Equity Line of Credit
Member Business Loans
All Other Loans


"tjtjjtjt" wrote:

I think an example might help.

tj

"Russell Hampton" wrote:

I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you. Russell

Perhaps one way to set it up,
if I've read it right ..

Let's say you have a reference table
in Sheet3
-------------
in cols A to B
data in row1 down

1 Unsecured Consumer Loans
2 Unsecured Visa Classic
3 Unsecured Visa Gold
etc

where col A = Collateral Codes
for the descripts in col B

And this table of sample data below is
in Sheet1
-------------
in cols A to B
data in row2 down

CollCode CredScore
1 586
1 605
2 633
3 640
1 643
2 643
2 651
1 656
3 659
3 665
2 692
1 696
etc

In Sheet2
-------------
Let's set it up as:

In A3 down will be listed the descripts:
Unsecured Consumer Loans
Unsecured Visa Classic
Unsecured Visa Gold
etc

In B2, put the label: CollCode

In C1:G2, set up the numeric "range" below:

----- 650 620 575 -----
710 709 649 619 574

Note: Leave C1 and G1 empty
(represented above by "-----")

Put

in B3:
=IF(ISNA(MATCH(A3,Sheet3!B:B,0)),"",INDEX(Sheet3!A :A,MATCH(A3,Sheet3!B:B,0))
)

in C3:
=SUMPRODUCT((Sheet1!$A$2:$A$30=$B3)*(Sheet1!$B$2:$ B$30=C$2))

in D3:
=SUMPRODUCT((Sheet1!$A$2:$A$30=$B3)*(Sheet1!$B$2:$ B$30=D$1)*(Sheet1!$B$2:$B
$30<=D$2))

Copy D3 across to F3

Put in G3:
=SUMPRODUCT((Sheet1!$A$2:$A$30=$B3)*(Sheet1!$B$2:$ B$30<=G$2)*(Sheet1!$B$2:$B
$300))

Now select B3:G3 and
copy down to populate the table

Suppress extraneous zeros from showing via:
Tools Options View tab Uncheck "Zero values" OK

Col B will extract the collateral code ("1", "2", "3", etc)
for the descripts in col A from the reference table in Sheet3

Cols C to G will return the counts of the numbers
in the CredScore col in Sheet1
falling within the numeric ranges concerned,
viz. respectively ..:

Col C: =710
Col D: 650 to 709
Col E: 620 to 649
Col F: 575 to 619
Col G: <=545


For the sample data in Sheet1,
you'll get in Sheet2:

--------------------------------- ---------- -------- 650 620 575 ---
--------------------------------- CollCode 710 709 649 619 574
Unsecured Consumer Loans 1 4 6 1 2
Unsecured Visa Classic 2 2 2
Unsecured Visa Gold 3 2 1
etc

(you could use the results table above to plot the chart)

Adapt the ranges:
Sheet1!$A$2:$A$30, Sheet1!$B$2:$B$30 to suit,
but note that you can't use entire col references
(e.g.: A:A, B:B, etc) in SUMPRODUCT
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Russell Hampton" wrote in
message ...
Hi TJ,

This is what I am working with. So for every 1 in the list I want it to
add
if the score is in a range. So given the example, how many are there that
are
less than 600? THe answer is one. And for 600-650 the answer is 5. Then I
will plug this into a chart like the one below. Does that help? I
appreciate
any insight you may have.

Collateral Code Credit Score
1 586
1 605
1 633
1 640
1 643
1 643
1 651
1 656
1 659
1 665
1 692
1 696
1 699
1 701
1 702
1 704
1 716
1 724
1 754
1 773

Chart example:
Jan-04
A B C D
710 + 650-709 620-649 575-619
Count Count Count Count
Unsecured Consumer Loans
Unsecured Visa Classic
Unsecured Visa Gold
New Vehicle Direct
Used Vehicle Direct
New Vehicle Indirect
Used Vehicle Indirect
First Mortgage
Second Mortgage Fixed
Home Equity Line of Credit
Member Business Loans
All Other Loans


"tjtjjtjt" wrote:

I think an example might help.

tj

"Russell Hampton" wrote:

I need to develop a matrix. I have the report with the raw data. In
one
column are numbers 1-900, the other column has scores. What I need is
to
count the numbers only if the score is in a certain range. I want to
come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any
advice
for this one? I can share the exact report if it will help. Thank you.
Russell

From your paragraph, these two formulas should get you your answers. To get
your data ranges set as below, you may need to alter the ranges from these
examples.
To calculate when the first column contains 1 and the second column number
is under 600:
=SUMPRODUCT(--(A2:A21=1),--(B2:B21<600))

For the first column equals 1 and the second column between 600 and 650:
=SUMPRODUCT(--(A2:A21=1),--(B2:B21600),--(B2:B21<650))

For an an explanation of what the formulas are doing:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You may also consider playing with the FREQUENCY Function. Embedding it in
an IF statement would likely let you work with the number ranges you set up
very easily.

Hope that helps.

tj

Based on

"Russell Hampton" wrote:

Hi TJ,

This is what I am working with. So for every 1 in the list I want it to add
if the score is in a range. So given the example, how many are there that are
less than 600? THe answer is one. And for 600-650 the answer is 5. Then I
will plug this into a chart like the one below. Does that help? I appreciate
any insight you may have.

Collateral Code Credit Score
1 586
1 605
1 633
1 640
1 643
1 643
1 651
1 656
1 659
1 665
1 692
1 696
1 699
1 701
1 702
1 704
1 716
1 724
1 754
1 773

Chart example:
Jan-04
A B C D
710 + 650-709 620-649 575-619
Count Count Count Count
Unsecured Consumer Loans
Unsecured Visa Classic
Unsecured Visa Gold
New Vehicle Direct
Used Vehicle Direct
New Vehicle Indirect
Used Vehicle Indirect
First Mortgage
Second Mortgage Fixed
Home Equity Line of Credit
Member Business Loans
All Other Loans


"tjtjjtjt" wrote:

I think an example might help.

tj

"Russell Hampton" wrote:

I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you. Russell

Thanks for the help. I think I have it now.

"tjtjjtjt" wrote:

From your paragraph, these two formulas should get you your answers. To get
your data ranges set as below, you may need to alter the ranges from these
examples.
To calculate when the first column contains 1 and the second column number
is under 600:
=SUMPRODUCT(--(A2:A21=1),--(B2:B21<600))

For the first column equals 1 and the second column between 600 and 650:
=SUMPRODUCT(--(A2:A21=1),--(B2:B21600),--(B2:B21<650))

For an an explanation of what the formulas are doing:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You may also consider playing with the FREQUENCY Function. Embedding it in
an IF statement would likely let you work with the number ranges you set up
very easily.

Hope that helps.

tj

Based on

"Russell Hampton" wrote:

Hi TJ,

This is what I am working with. So for every 1 in the list I want it to add
if the score is in a range. So given the example, how many are there that are
less than 600? THe answer is one. And for 600-650 the answer is 5. Then I
will plug this into a chart like the one below. Does that help? I appreciate
any insight you may have.

Collateral Code Credit Score
1 586
1 605
1 633
1 640
1 643
1 643
1 651
1 656
1 659
1 665
1 692
1 696
1 699
1 701
1 702
1 704
1 716
1 724
1 754
1 773

Chart example:
Jan-04
A B C D
710 + 650-709 620-649 575-619
Count Count Count Count
Unsecured Consumer Loans
Unsecured Visa Classic
Unsecured Visa Gold
New Vehicle Direct
Used Vehicle Direct
New Vehicle Indirect
Used Vehicle Indirect
First Mortgage
Second Mortgage Fixed
Home Equity Line of Credit
Member Business Loans
All Other Loans


"tjtjjtjt" wrote:

I think an example might help.

tj

"Russell Hampton" wrote:

I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1-900, the other column has scores. What I need is to
count the numbers only if the score is in a certain range. I want to come up
with a chart that looks like:
A B C D
Type 1
Type 2
Type 3

I can't embed a countif, inside of a countif. Does anyone have any advice
for this one? I can share the exact report if it will help. Thank you. Russell