SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA
 

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.

SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA
 

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

--
Don Guillett
SalesAid Software

"Andrew C" <Andrew wrote in message
...
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.

SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA
 

This seemed to work using your data.

=SUMPRODUCT(--(A1:A11="H"),--(C1:C11200),(D1:D11))

The total was 95,950.

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=496758