COUNTIF with multiple conditions from different sheets

[alinpion]

Hello !!

I got two sheets ("data" and "statistic") in one I got two clomuns one with numbers and another with names like in the example:
in "data" sheet :

A ---B
1 ---John
0 ---Danny
6 ---Mary
0 ---John
24 --Danny
0 ---Mary

I want (in sheet two "statistic") to count each time 0 appears on each name

something like:

IF B="John" and A=0 COUNT 1

 

[Sektor]

= COUNTIFS(data!A:A,0,data!B:B,"John")

 

[alinpion]

= COUNTIF(data!A:A,"John")

This I konw but I need the formula to count if bouth conditions are true B=John and A=0

The table that I presented above is just an example ; the table that I work with is much bigger and 0 appears many times for one name.

 

[Sektor]

I corrected my formula. See previous post!

 

[alinpion]

= COUNTIFS(data!A:A,0,data!B:B,"John")

I got excel2003 and this formula doesn't work I think COUNTIFS is for excel 2007

 

[Sektor]

Press Ctrl+Shift+Enter:
= SUM((data!A:A=0)*(data!B:B="John"))

 

[Aladin Akyurek]

Hello !!

I got two sheets ("data" and "statistic") in one I got two clomuns one with numbers and another with names like in the example:
in "data" sheet :

A ---B
1 ---John
0 ---Danny
6 ---Mary
0 ---John
24 --Danny
0 ---Mary

I want (in sheet two "statistic") to count each time 0 appears on each name

something like:

IF B="John" and A=0 COUNT 1

Also...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7=0,IF($B$2:$B$7=E2,1))))

Or, just enter:

=SUMPRODUCT(--ISNUMBER($A$2:$A$7),--($A$2:$A$7=0),--($B$2:$B$7=E2))

where E2 houses a value like John.

 

[alinpion]

Press Ctrl+Shift+Enter:
= SUM((data!A:A=0)*(data!B:B="John"))

Is not working ;r esponse #NUM!

Is there a way that I could replace the COUNTIFS with SUMPRODUCT and COUNTIF

All I need is a COUNTIF that has two criteria : if = with "John" and with "0" count 1

 

[alinpion]

Also...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7=0,IF($B$2:$B$7=E2,1))))

Or, just enter:

=SUMPRODUCT(--ISNUMBER($A$2:$A$7),--($A$2:$A$7=0),--($B$2:$B$7=E2))

where E2 houses a value like John.

First works formula works
=SUM(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7=0,IF($B$2:$B$7=E2,1))))
I just replaced E2 with John

Thanks for your help !!!

 

[Aladin Akyurek]

First works formula works
=SUM(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7=0,IF($B$2:$B$7=E2,1))))
I just replaced E2 with John

Thanks for your help !!!

You are welcome. Thanks for providing feedback.