Sumproduct with criteria in different sheets (rows & columns)

[drluke]

I have 2 data worksheets:
Customers:

<tbody> [TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 78, bgcolor: transparent"] A [/TD]
[TD="width: 94, bgcolor: transparent"] B [/TD]
[TD="width: 207, bgcolor: transparent"] C [/TD]
[TD="width: 26, bgcolor: transparent"] 1 [/TD]
[TD="width: 78, bgcolor: transparent"] A001 [/TD]
[TD="width: 94, bgcolor: transparent"] J Smith [/TD]
[TD="width: 207, bgcolor: transparent"] Address1 [/TD]
[TD="width: 26, bgcolor: transparent"] 2 [/TD]
[TD="width: 78, bgcolor: transparent"] D325 [/TD]
[TD="width: 94, bgcolor: transparent"] R Brown [/TD]
[TD="width: 207, bgcolor: transparent"] Address1 [/TD]
[TD="width: 26, bgcolor: transparent"] 3 [/TD]
[TD="width: 78, bgcolor: transparent"] S589 [/TD]
[TD="width: 94, bgcolor: transparent"] P Day [/TD]
[TD="width: 207, bgcolor: transparent"] Address1 [/TD]
</tbody>

Sales

<tbody> [TD="width: 28, bgcolor: transparent"] [/TD]
[TD="width: 76, bgcolor: transparent"] A [/TD]
[TD="width: 76, bgcolor: transparent"] B [/TD]
[TD="width: 66, bgcolor: transparent"] C [/TD]
[TD="width: 66, bgcolor: transparent"] D [/TD]
[TD="width: 76, bgcolor: transparent"] E [/TD]
[TD="width: 28, bgcolor: transparent"] 1 [/TD]
[TD="width: 76, bgcolor: transparent"] Customer [/TD]
[TD="width: 76, bgcolor: transparent"] Jan-18 [/TD]
[TD="width: 66, bgcolor: transparent"] Feb-18 [/TD]
[TD="width: 66, bgcolor: transparent"] Mar-18 [/TD]
[TD="width: 76, bgcolor: transparent"] Apr-18 [/TD]
[TD="width: 28, bgcolor: transparent"] 2 [/TD]
[TD="width: 76, bgcolor: transparent"] A001 [/TD]
[TD="width: 76, bgcolor: transparent"] 200 [/TD]
[TD="width: 66, bgcolor: transparent"] 300 [/TD]
[TD="width: 66, bgcolor: transparent"] 200 [/TD]
[TD="width: 76, bgcolor: transparent"] 900 [/TD]
[TD="width: 28, bgcolor: transparent"] 3 [/TD]
[TD="width: 76, bgcolor: transparent"] S589 [/TD]
[TD="width: 76, bgcolor: transparent"] [/TD]
[TD="width: 66, bgcolor: transparent"] [/TD]
[TD="width: 66, bgcolor: transparent"] [/TD]
[TD="width: 76, bgcolor: transparent"] [/TD]
[TD="width: 28, bgcolor: transparent"] 4 [/TD]
[TD="width: 76, bgcolor: transparent"] A001 [/TD]
[TD="width: 76, bgcolor: transparent"] 500 [/TD]
[TD="width: 66, bgcolor: transparent"] 100 [/TD]
[TD="width: 66, bgcolor: transparent"] 600 [/TD]
[TD="width: 76, bgcolor: transparent"] 1000 [/TD]
[TD="width: 28, bgcolor: transparent"] 5 [/TD]
[TD="width: 76, bgcolor: transparent"] C522 [/TD]
[TD="width: 76, bgcolor: transparent"] [/TD]
[TD="width: 66, bgcolor: transparent"] [/TD]
[TD="width: 66, bgcolor: transparent"] [/TD]
[TD="width: 76, bgcolor: transparent"] [/TD]
</tbody>

In my mastersheet I am trying to sum all the sales to A001 using SUMPRODUCT:
In my mastersheet col A contains the customer numbers, and B1:M1 the months of theyear. In B2 I have:
=SUMPRODUCT((‘Customers’!$A$1:$A$3=A1)*(‘Sales’!$B$1:$E$1=B1)*’Sales’!$B$2:$E$5)

The result Iget is #VALUE error. Any advice would beappreciated.

 

[Eric W]

I don't see that you need to reference the customer sheet at all. You're looking up by the customer number, which already exists on your Sales sheet. Try this formula in B2 of your master sheet:

=SUMPRODUCT((Sales!$A$2:$A$5=$A2)*(Sales!$B$1:$E$1=B$1)*Sales!$B$2:$E$5)