SUMPRODUCT not working - what is wrong?

[Camel123]

Hi,

I want the formula in Sheet1 to sum total sales for all months per customer per order.

Column B in both sheets= customer number. Column E in both sheets= order number. Q5-AG280= sales per month per customer and order and range to be summed.

{=SUMPRODUCT(('Sheet2'!B:B='Sheet1'!B4)*('Sheet1'!E:E='Sheet1'!E4)*('Sheet2'!Q5:AG280))}

Tried the formula above but not working, any tips?

 

[RoryA]

What is the actual error?

 

[Special-K99]

SUMPRODUCT works on a range of numbers ONLY, if you have text within the data range you will get errors.
e.g.
1
2
3
will not produce any SUMPRODUCT errors
1
A
3
will
This also creates errors if you have a space (not a blank cell) in the data

 

[Camel123]

SUMPRODUCT works on a range of numbers ONLY, if you have text within the data range you will get errors.
e.g.
1
2
3
will not produce any SUMPRODUCT errors
1
A
3
will
This also creates errors if you have a space (not a blank cell) in the data

I have some customer numbers starting with a letter, for instance "T43567" and some with only numbers. When applying the function to the same data but in the same sheet, the text mixed with numbers does not matter it seems. Values are still returned correct.

 

[RoryA]

It's the data in 'Sheet2'!Q5:AG1000 that must all be numeric or blank. So, again, what's the actual error?

 

[Camel123]

It's the data in 'Sheet2'!Q5:AG1000 that must all be numeric or blank. So, again, what's the actual error?

Evaluating the formula shows that the critera's are found (true), but 0 is returned, then this 0 is multiplied with the sum range= 0.

In the Q5:AG1000 range I have formulas retrieving all sales data. If no sales, then the formula returns "", using isnumber formula shows ""=false, where numbers exist in this range due to sales, isnumber shows true.

 

[RoryA]

0 is not an error, in Excel terms. You may think it's the wrong result, but it's not an error like #VALUE ! which I think is what we all thought you meant.

On that basis, I'd say your criteria don't actually match (eg you're comparing a number code against a number stored as text). The Evaluate formulas dialog is frankly a little flaky.

 

[Camel123]

0 is not an error, in Excel terms. You may think it's the wrong result, but it's not an error like [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! which I think is what we all thought you meant.

On that basis, I'd say your criteria don't actually match (eg you're comparing a number code against a number stored as text). The Evaluate formulas dialog is frankly a little flaky.

It did return #value initially until changing some things on the way basedo n your guy's input anyway, since the critera's are identified and "true" returned, should it not interpret the true as "1" and retrieve all values in the sum range? The criteria's have the same format on both sheets, customer number classified as text are classified as such on both sheets, same goes for numbers.

 

[RoryA]

You'd get #VALUE due to the "" in your formula results. You can't multiply "" by anything.

On that basis, you would need an array formula:

=SUMPRODUCT(('Sheet2'!B5:B1000='Sheet1'!B5)*('Sheet2'!E5:E1000='Sheet1'!E5)*(IF('Sheet2'!Q5:AG1000="",0,'Sheet2'!Q5:AG1000)))

 

[DataBlake]

then the formula returns "", using isnumber formula shows ""=false

you should change the "" to an actual error such as "error" and then replace "error" with "" using find/replace
for some reason i had a similar issue where there was still a value in the cell from a formula being there
so it is still possible that its calculating 0*x*y = 0

or perhaps create an if statement with your calculation to avoid 0's and blank cells

 

[Repush]

start with a smaller data set on one sheet and share this data with us
try
=SUMPRODUCT(--(B5:B10=B5),--(E5:E10=E5),(Q5:AG10))
if this works out . . . expand and move

still . . .it's strange that you enter this as an array formula (Ctrl+Shift+Enter) and that the colums are compared to the colum's top cell