SUM problem with too many results

  • Thread starter Thread starter Tom Drill
  • Start date Start date
T

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!
 
This formula only provided an answer of "0". It should
be "2". Thank you, Arvi! But I'll still need help.
 
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
 
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
 
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
<>""))
 
Back
Top