Sumproduct with criteria in different sheets (rows & columns)

[drluke]

I have 2 data worksheets:
Customers:

	A	B	C
1	A001	J Smith	Address1
2	D325	R Brown	Address1
3	S589	P Day	Address1

<tbody> </tbody>

Sales

	A	B	C	D	E
1	Customer	Jan-18	Feb-18	Mar-18	Apr-18
2	A001	200	300	200	900
3	S589
4	A001	500	100	600	1000
5	C522

<tbody> </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)