MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countif problems

Posted by Mark Jolly on April 30, 2001 1:23 AM

I need to find out the count of yes/no against clinets in a table, such as this

30/4 DAVE Y
30/4 MARK N
30/4 DAVE Y

DAVE Y=2 N=0
MARK Y=0 N=1

i have tried with countif functions and have not been able to do it, but have with pivot tables. Is this possible with functions/formulas


Mark Jolly

Posted by Aladin Akyurek on April 30, 2001 2:12 AM


I'll assume that your example data ccupy the range A1:C3.

Enter the names of interest in D from D2 on.
Enter Y in E1 and N in F1.

Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time to enter) the following formula in E2:


Copy this across to E2 then down as far as needed.



Posted by Dave Hawley on April 30, 2001 2:16 AM

Hi Mark

You could also use and array formula like:

Or one of the Database functions, ie; DCOUNTA
There is a lot of examples in the Excel help on these.

But if you have got a Pivot Table, then that is by far the best way, Folowed by the DCOUNTA and lastly the Array formula.

I know the temtation is there to use an array formula as it's easier, read my Webpage on arrays first.


OzGrid Business Applications