MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Aladin

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.

Aladin

Posted by Dave Hawley on April 24, 2001 1:26 AM

Hi Withaar


If you are going to be dealing with a lot of rows or need to use the formula many times, I would recommend using the DCOUNTA formula. This can handle multiple criteria. There are some good examples in the help for these formulas.


Dave

OzGrid Business Applications