Sumif with references on different tabs

[Irishrich]

I have a spreadsheet containing sales by customer and material by week. On a
seperate spreadsheet I have the dates during which each customer and material
were on promotion. Each customer has a number of different promotions across
the year for the same material so I was planning to use a sum if to specify
my date range and an embedded vlookup to specify the specific row I wanted to
add the sales in. Simplified version of the 2 sheets are below

Sheet 1 (promo details)

Promotion Customer Material Buying from Buying to
Test 1 Cust 1 SKU1 01/01/2009 17/01/2009
Test 2 Cust 2 SKU2 02/02/2009 02/03/2009
Test 3 Cust 3 SKU3 02/02/2009 02/03/2009

Sheet 2 (sales data)

Week
Customer Material 01/01 08/01 15/01 22/01 29/01 05/02
Cust 1 SKU1 5 18 13 2 2
Cust 1 SKU2 5 8 9
Cust 1 SKU3 4 3 3 3 2 18
Cust 2 SKU1 5 18 13 2 2
Cust 2 SKU2 5 8 9
Cust 2 SKU3 4 3 3 3 2 18
Cust 3 SKU1 5 18 13 2 2
Cust 3 SKU2 5 8 9
Cust 3 SKU3 4 3 3 3 2 18

I cannot figure out how to make the 2 sheets talk to each other. Any ideas?

 

[Bob Phillips]

Try this

=SUMPRODUCT((Week!$A$2:$A$20=B2)*(Week!$B$2:$B$20=C2)*(Week!$C$1:$H$1>=D2)*(Week!$C$1:$H$1<=E2)*(Week!$C$2:$H$20))

 

[Luke M]

From my tests, this formula appears to work. You may need to adjust the range
sizes inside the SUMPRODUCT function to meet your actual data:

=SUM(OFFSET('Sales Data'!$B$1,SUMPRODUCT(--('Sales
Data'!$A$2:$A$25=B2),--('Sales
Data'!$B$2:$B$25=C2),ROW($B$2:$B$25))-1,MATCH(D2,'Sales
Data'!$C$1:$Z$1),,COUNTIF('Sales Data'!$C$1:$Z$1,">="&D2)-COUNTIF('Sales
Data'!$C$1:$Z$1,">"&E2)))

Formula uses the OFFSET function to define a region of cells, first
determining what row to look at, what starting column to look at, and how
many columns to look at. The SUMPRODUCT function returns the correct row to
look at. The MATCH function returns the correct starting column. The 2
COUNTIF funtions determine how many columns to look at.

 

[Irishrich]

*&^@£$""* Clicked on wrong button when posting reply!

Formula above just returns a zero value which is what a simplified version
of the sumif is doing.

 

[Irishrich]

Will try to get my head around offset. Hopefully it will work