Count if - 2 conditions

[orquidea]

Hi:

How would I write A "COUNT IF AND" formula? I tried

COUNT IF AND(E5:E30,"MAMA"),(B:5:C30,"4"), but there is an error somewhere.

Thanks in advance.
Escarly

 

[T. Valko]

COUNT IF AND(E5:E30,"MAMA"),(B:5:C30,"4"),

It depends on how you want this to count. You're referencing a 2 column
range with B5:C30. What result do you expect with this sample:

......B.....C.....E
......4.....4....Mama
......1.....4....Mama

Should the result be 2 or 3?

 

[CLR]

=SUMPRODUCT((E5:E30="mama")*(C5:C30=4))

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Good catch Biff...........I was reading out of my dominant eye again........<g>

Vaya con Dios,
Chuck, CABGx3

 

[orquidea]

I excpect 2, because rows have the 2 conditions "mama" and "4"

 

[T. Valko]

Try this:

=SUMPRODUCT(--((B5:B30=4)+(C5:C30=4)>0),--(E5:E30="mama"))

 

[orquidea]

thanks

Try this:

=SUMPRODUCT(--((B5:B30=4)+(C5:C30=4)>0),--(E5:E30="mama"))

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

thanks