SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA

[Andrew C]

I have the following grid excerpt:

WHSE COST QTY Total
H $28.57 864 14583
H $5.13 7424 64566
H $98.28 115 2833
E $14.76 1050 12000
H $65.06 194 2194
H $12.20 755 9567
E $14.76 900 7050
H $189.17 43 546
1 $46.90 265 1921
H $12.20 490 7234

I have attempted using the SUMPRODUCT function to find all the quantities in
WHSE H which are greater than 200 (for example), but all I get for an answer
is 0. It appears the function does not like me to use numbers for a
criterion since I can get it to work with words as fields but not
numbers...or could it be the greater or lesser operators? I am just finding
out how to do this so any help from the experts would be appreciated.

 

[Don Guillett]

try something like this
=sumproduct((a2:a200="H")*(b2:b200>200)*c2:c200)

 

[SteveG]

This seemed to work using your data.

=SUMPRODUCT(--(A1:A11="H"),--(C1:C11>200),(D111))

The total was 95,950.

Cheers,

Steve