# 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.

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

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