Home 
Search 
Today's Posts 
#1




countif statement
I need to develop a matrix. I have the report with the raw data. In one
column are numbers 1900, 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 
#2




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 1900, 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 
#3




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 1900, 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 
#4




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 600650 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: Jan04 A B C D 710 + 650709 620649 575619 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 1900, 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 
#5




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 600650 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: Jan04 A B C D 710 + 650709 620649 575619 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 1900, 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 
#6




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 600650 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: Jan04 A B C D 710 + 650709 620649 575619 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 1900, 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 
#7




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 600650 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: Jan04 A B C D 710 + 650709 620649 575619 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 1900, 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
IF Statement with Average Function results in #Value!  Excel Discussion (Misc queries)  
Statement  Excel Worksheet Functions  
Countif  Countif  Excel Worksheet Functions  
countif  Excel Worksheet Functions  
countif, again  Excel Worksheet Functions 