Conditional COUNTIF?
Posted by REbecca on June 22, 2001 1:48 PM
I think Zen and Aladin misunderstood Jarrod's question, and I have a similar one so
let me see if I can make it clearer...
I want to count only the Apples that are red, i.e. fulfilling the conditions of Red AND Apple. So I need to check against
one column for the condition of "Red" and THEN tally those occurrences of
"Apple" in another column that fulfill the condition of being red.
Actually, just to make it more difficult, I would ideally like to find all the
"Apples" that fulfill the multiple negative conditions of being NOT green, NOT blue, and NOT yellow.
What I need, basically, is a Conditional COUNTIF wizard, like the conditional COUNTSUM wizard that
already exists. Anyone know how to do this?
Thanks a lot! I stumbled on this site and its a godsend...
Posted by Rebecca on June 22, 2001 1:50 PM
I Meant...
I meant a conditional COUNTIF like the Conditional SUMIF wizard....
Posted by Aladin Akyurek on June 22, 2001 2:17 PM
Multiconditional Count
Rebecca
I didn't look yet at my reply to Jarrod. I'll do so in a minute.
Back to your question:
Lets assume the following data in A1:B7.
{"Apple","Red"; "Pear","Green"; "Apple","Green"; "Apple","Yellow"; "Apple","Blue"; "Apple","Red"; "Orange","Orange"}
In C1 array-enter: =SUM((A1:A7="Apple")*(B1:B7="Red"))
In order to array-enter an array-formula you need to hit CONTROL+SHIFT+ENTER (not just ENTER).
The above formula results in a count of 2, meaning there are 2 apples having color red.
If you want to count non-red apples, just change the second term's = operator to the <> operator.
What about counting all apples that are NOT blue or NOT yellow: Array-enter
=SUM((A1:A7="Apple")*(B1:B7<>"Blue")*((B1:B7<>"yellow")))
Aladin
===============
Posted by Ben O. on June 22, 2001 2:34 PM
Re: Conditional Sum vs. Condtional Count
Conditional SUM formulas can be used to count just as easily as they can be used to sum. Take the following formula:
{=SUM(IF(A2:A20="Apple",1,0))}
For every instance of "Apple" in A2:20, it'll count 1, because a 1 is specify in the THEN part of the IF-THEN function. If you want to add more conditions, you can keep nesting IF statements, like this:
{=SUM(IF(A2:A20="Apple",IF(B2:B20="Red",1,0),0))}
-Ben
Posted by Aladin Akyurek on June 22, 2001 2:43 PM
Re: Conditional Sum vs. Condtional Count
Ben
Nested IFs can be easily avoided with the Boolean array formulas. See my reply above in this thread. Indeed, one must be aware of the distinction Multiconditional Count vs Multiconditional Sum.
Regards,
Aladin
Posted by Ben O. on June 23, 2001 12:57 PM
Re: Multiconditional Count
Thanks for the advice, Aladin. That will make constructing conditional sum formulas a lot easier in the future. I learned to create them by using the Conditional Sum Wizard, which nests IF statements. This really is a lot simpler. Thanks,
-Ben
Posted by Rebecca Johnson on June 25, 2001 10:59 AM
Re: Conditional Sum vs. Condtional Count
You guys are magic--thanks!
