sumproduct
 

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


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=485796

sumproduct
 

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


cjjoo wrote:
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

sumproduct
 

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

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

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

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

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

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

sumproduct function?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=485796