SUM problem with too many results

[Tom Drill]

Room1 table 10 John
Room1 table 12 John
Room2 table 12 Jane
Room1 table Jane
Room3 table 10 John
Room1 chair 5 Jane
Room1 chair 8 Jane
Room2 chair Jane
Room3 chair 5 Jane
Room1 desk 12 John
Room2 desk John

I need a formula that will filter by "Room1" and "table"
and count the entries in column C. I wrote =SUM(IF
(A1:A11="Room1",AND(B1:B11="table",1,0)),COUNT
(C1:C11,1,0)), but it's answering 10 instead of the
expected 2. Please help! Thanks!

 

[Arvi Laanemets]

Hi

=SUMPRODUCT((A1:A11="Room1")*(B1:B11="table1"))

 

[Tom Drill]

This formula only provided an answer of "0". It should
be "2". Thank you, Arvi! But I'll still need help.

 

[Frank Kabel]

Hi Tom
try
=SUMPRODUCT(--(A1:A11="Room1"),--(B1:B11="table"))

 

[Arvi Laanemets]

Hi

Then there aren't any rows with both conditions filled at same time. Maybe
you have some additional spaces in search or searched values. Try to copy
the values for "Room1" and "table1" in formula from some row in your table

 

[Tom Drill]

Arvi:

Thanks for your reply. I tried the following formula:

=SUMPRODUCT(--($A$1:$A$11="Room1"),--($B$1:$B$11="table"))

This worked for the most part because it provided the
answer of 3. The answer should be 2, but the formula is
counting blank spaces in Column C.

Any help to stop counting blank spaces would be
appreciated.

Tom

 

[Frank Kabel]

Hi
but these rows have values in column A and B?. You may try
=SUMPRODUCT(--($A$1:$A$11="Room1"),--($B$1:$B$11="table"),--($C$1:$C$11
<>""))