countif calculation problems

Posted by Karan on January 11, 2001 1:53 PM

I need to perform a countif calculation based on two sets of criteria. I have tried using if() statements and countif() statements without success. I have tried dcount() which works fine for the total, but I need the data broken down further.

Column A contains "Area" and Column AI contains "Month". I need a formula that will count the number of entries that contain value "X" in "Area" [countif(a3:a184,"X")] and value "Y" in "Month" [countif(ai3:ai184,"Y")]. I have tried nesting the countif also without success. Seems a relatively simple task, but...

Any help is greatly appreciated.

Posted by Mark W. on January 11, 2001 2:37 PM

If cells A1:A4 contain {"Area";"X";"";"X"} and
cells AI1:AI4 contain {"Area";"X";"";"X"}, then
{=SUM((A2:A4="X")*(AI2:AI4="Y"))} will return 1.

Posted by Karan on January 11, 2001 2:54 PM

Thank you so much! I forgot to mention that column A is text and column ai is numerical, but the sum function works. I don't really understand how, but I really do appreciate your help!

Karan

Posted by Mark W. on January 11, 2001 3:04 PM

If column AI:AI only contains boolean (1s or 0s)
or empty cells, you could also use:

{=SUM((A2:A4="X")*AI2:AI4)}