CountA if true in two koloms

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

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?

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

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