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

SO THAT IT
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

thanks

Mark Jolly

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

Mark,

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:

=SUM(($B$1:$B$3=$D2)*($C$1:$C$3=E$1))

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

Aladin

==============

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

Hi Mark

You could also use and array formula like:
=SUM(($A$2:$A$400="Dave")*($B$2:$B$400="Y"))

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.

Dave

OzGrid Business Applications