sumproduct

C

cjjoo

this thing ahs been bugging me for quite some time . Hope that someone
can help me!


i tried to use sumproduct to calculate the sum of a product based on a
few criteria but the result i got is ZERO.

the formula is
SUMPRODUCT(--($E$2:$E$11=4086),--($B2:$B$11="Oct"),--($J$2:$J$11="Domestic"),$H$2:$H$11)

4086 is a number i want to find . Both the Oct and Domestic are
descriptions
 
R

Roger Govier

Hi

Is the 4086 a number, or text?
If it is text, then you need to wrap it in double quotes "4086"
SUMPRODUCT(--($E$2:$E$11="4086"),--($B2:$B$11="Oct"),--($J$2:$J$11="Domestic"),$H$2:$H$11)

You mention 4086 is a number you want to "find". Is it part of a larger
number like 123408679?
If so, then
SUMPRODUCT(--(ISNUMBER(FIND(4086,$E$2:EB$11))),--($B2:$B$11="Oct"),--($J$2:$J$11="Domestic"),$H$2:$H$11)
will count all occurrences of 4086 within the range, including any text
4086's and numeric 4086's.

Regards

Roger Govier
 
C

cjjoo

harlow, actually 4086 is a number that is key in . As for the scenari
i cited

above, i try to trouble shoot the problem by inserting only tw
criteria: the

column with " domestic" and the column that i want to sum. But
realised that

the sumproduct cannot picked out that word "domestic" . It worke
perfectly

for the rest like : "solid" and " Liquid" . This is very puzzlin
...... :confused:

Is it becoz of the font of the word or anything else that may affec
the result of a

sumproduct function
 
Top