Problem with CountIf
Posted by Stella on June 22, 2001 12:17 PM
I'm using Excel 2000. I have a large file of information. There are 2,622 rows of information with columns through BD.
The information we need will only use 2 of these columns. One is an identifier column for type of client, One is a total column with figures from $0 to $30,000.
The goal is to have excel determine how many rows fall between ranges of dollars.
The first level of =30000 produced the correct number of rows, however, calculating the next level of >=25000 <30000 was not favorable. Please help. There are 8 more levels we need to calculate.
Thank you. Stella
Posted by Ben O. on June 22, 2001 12:28 PM
Try using the Conditional Sum Wizard. It will let you easily add conditions and automaticaly construct the formula. However, I believe it has a limit of 4 conditions. If you need 8, you'll have to use a lot of nested IF statements. Here's what it looks like with two conditions:
=SUM(IF($B$2:$B$500>=25000,IF($B$2:$B$500<30000,1,0),0))
I hope this helps,
-Ben
Posted by Ben O. on June 22, 2001 12:30 PM
One more thing...
{=SUM(IF($B$2:$B$500>=25000,IF($B$2:$B$500<30000,1,0),0))}
This is an array formula, so remember to press Ctrl + Shift + Enter when you enter it.
-Ben
Posted by IML on June 22, 2001 12:31 PM
Include quotes around the operating and the & sign. For example:
=COUNTIF(A1:A4,">="&D3)
Hopefully that will help.
Good luck
Posted by Aladin Akyurek on June 22, 2001 2:50 PM
Another Multiconditional Count issue...
Stella
Do you want the counts per type of client?
Aladin
Posted by IML on June 22, 2001 3:03 PM
After a second read...
After looking at Aladin's comment I think I mis-read (not usual).
If your numbers are A1:A10, you could use the following formla
=SUM((A1:A10>=25000)*(A1:A10<30000)) to get counts of what you are after.
If you are after specific firms, lets say "firma" that is listed in column B the formula would be
=SUM((A1:A10>=25000)*(A1:A10<30000)*(B1:B10="firma"))
After this formula is typed in, hit enter while control and shift are depressed or it will not work properly. If you've done it right, brackets will appear around it in the formula bar.
Posted by IML on June 22, 2001 3:14 PM
After a third read
I couldn't get Ben O formula to work, but it was me be stupid. It worked fine for me, I just wasn't used to the format. Sorry, that this was aleady asked and answered.
