# CountA if true in two koloms

Posted by Withaar on April 23, 2001 7:44 AM

for exemple

A B C
1 aa yes
2 aa no
3 bb yes
4 cc no
5 aa yes

I like to count all aa with yes, aa with no, all bb with yes .....

Posted by Aladin Akyurek on April 23, 2001 9:22 AM

Using your example, put all distinct values in A1:A5 in C from C2 on. Put Yes in D1 and No D2.

Array-enter (that is, hit control+shift+enter at the same to enter) the following formula in D2:

=SUM((\$A\$1:\$A\$5=\$C2)*(\$B\$1:\$B\$5=D\$1))

and copy this across and down.

Posted by Withaar on April 23, 2001 2:24 PM

Thanks, but it does not do the job.
It only counts to 1 (present/not present so to say).
I'm trying to count all aa with yes with can be up to a 1000 or more.
I tryed to make a macro with the use of autofilter but it still counted all and not only the visible part after the filter.

So, who can help?

Posted by Aladin Akyurek on April 23, 2001 10:21 PM

Withaar : for exemple : 1 aa yes : 2 aa no : 3 bb yes : 4 cc no : 5 aa yes

This is what I get when appled to your example.

{"","yes","no";"aa",2,1;"bb",1,0;"cc",0,1}

aa-yes = 2
aa-no = 1
bb-yes = 1
....
etc.

I see that I said: "Put Yes in D1 and No D2". That should have been:

D1 and E1 respectively.

Type the formula in D2 and immediately hit CONTROL+SHIFT+ENTER at the same time. Then copy it using fill handle to E2 then down.