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?

 

[tyija1995]

It's the final condition that is causing issues, I think it must reference something more specific?

 

[bobsan42]

I don't understand exactly what not working means but SUMPRODUCT works with same size linear ranges - the ranges you try to sumproduct have nothing in common.

 

[Camel123]

I don't understand exactly what not working means but SUMPRODUCT works with same size linear ranges - the ranges you try to sumproduct have nothing in common.

I changed the ranges to but still only returning errors.

{=SUMPRODUCT(('Sheet2'!B5:B1000='Sheet1'!B5)*('Sheet1'!E5:E1000='Sheet1'!E5)*('Sheet2'!Q5:AG1000))}

 

[Camel123]

I think it has something to do with using the SUMPRODUCT across multiple sheets. I tried the same formula with all cell values in the same sheet and then it worked. How do I make it compatible when referring to multiple sheets?

 

[RoryA]

Did you really mean to compare Sheet1!E5:E1000 rather than Sheet2?

 

[Camel123]

Did you really mean to compare Sheet1!E5:E1000 rather than Sheet2?

I meant: (sorry)
{=SUMPRODUCT(('Sheet2'!B5:B1000='Sheet1'!B5)*('Sheet2'!E5:E1000='Sheet1'!E5)*('Sheet2'!Q5:AG1000))}

 

[RoryA]

That should work (also doesn't need to be an array formula) as long as none of the ranges have errors in, and no cells in Q5:AG1000 have text in them.

 

[Joe4]

I see your use of squiggly brackets. Are you entering this as an array formula?
I don't believe that is necessary to do this with SUMPRODUCT.

Do you have any errors in any of your ranges? If so, that will cause an error.
If you cannot find the error, bring the formula down, adding in one part at at time. When the error occurs, focus on the last part you added.


EDIT: Yeah, what Rory said! I got interrupted why replying, and didn't see his reply.

 

[Camel123]

I see your use of squiggly brackets. Are you entering this as an array formula?
I don't believe that is necessary to do this with SUMPRODUCT.

Do you have any errors in any of your ranges? If so, that will cause an error.
If you cannot find the error, bring the formula down, adding in one part at at time. When the error occurs, focus on the last part you added.


EDIT: Yeah, what Rory said! I got interrupted why replying, and didn't see his reply.

I tried both with and without squiggly brackets. I have no errors. Format is same on both sheets as well. I brought the formula down starting with one criteria but still just generating error. I read about people adding "indirect" to SUMPRODUCT when using it across multiple sheets. Don't know if it has to do with this but I am an Excel-beginner and not familiar with INDIRECT function yet.