SumProduct on Multiple Criteria

I got a question why the sumproduct can not detect the value on the second cateria that i want

=SUMPRODUCT(($D$2:$D$15="Cash")*($E$2:$E$15="1"))
but it give me zero suppose have 1. Why like this ? Please help
Date Invoice Company ID Method Amount
15/01/2008 110187 Check 57.62
16/01/2008 110188 Cash 1.00
16/01/2008 110189 CC 23.74
16/01/2008 110190 Check 49.12
17/01/2008 110191 Check 17.35
18/01/2008 110192 CC 62.95
18/01/2008 110193 Cash 1.41
18/01/2008 110194 Check 23.62
19/01/2008 110195 Check 58.83
20/01/2008 110196 Check 81.07
20/01/2008 110197 Check 51.31
21/01/2008 110198 CC 36.92
21/01/2008 110199 Cash 82.68
21/01/2008 110200 CC 53.41

Try:

=SUMPRODUCT(($D$2:$D$15="Cash")*($E$2:$E$15=1))

I tend to prefer:

=SUMPRODUCT(--($D$2:$D$15="Cash"),--($E$2:$E$15=1))

I usually end up coercing more values than I need to but, hey, it also usually works.


Regards, TMS

You get only 4 collumn. You want to find a value in collumn 5 (E).

This is empty and so it returns zero.

Maybe that's the reason.

@oeldere: I was assuming that this was just the poor representation of the data, though you could be right.

I think column D is Method which is "cheque", "cash", etc., which means that column E is "amount ... which has one matching record.

Regards