Re: Sumif with references on different tabs

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

---
HTH

Bob Phillips

"Irishrich" <Irishrich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8660FAA6-FF8A-496C-88DF-20C7A8850DC2@xxxxxxxxxxxxxxxx
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?



.

Relevant Pages

  • Re: Sumif with references on different tabs
    ... "Bob Phillips" wrote: ... Each customer has a number of different promotions ... Promotion Customer Material Buying from Buying to ... I cannot figure out how to make the 2 sheets talk to each other. ...
    (microsoft.public.excel.misc)
  • Re: changing the order
    ... Knit can go into tops for DD and cuffs, etc. for other shirts/pants and fleece can become the back of another quilt or another pair of mitts. ... The sheets are donations from one of the local hospitals, but they all need mending of some sort. ... sewed up toile for today's AM customer, ... Tomorrow: Fitting for customer ...
    (rec.crafts.textiles.quilting)
  • Re: RE: User Forms
    ... A+, Joanne! ... my app. ... columns of info for each customer anyway so I would be doing quite a bit ... There are 62 sheets on the master - only maybe half a dozen ...
    (microsoft.public.excel.newusers)
  • Re: RE: User Forms
    ... my app. ... columns of info for each customer anyway so I would be doing quite a bit ... There are 62 sheets on the master - only maybe half a dozen ... until user needs to look up another customer's info. ...
    (microsoft.public.excel.newusers)
  • Re: RE: User Forms
    ... issue-by-issue, in separate threads. ... my app. ... columns of info for each customer anyway so I would be doing quite a bit ... There are 62 sheets on the master - only maybe half a dozen ...
    (microsoft.public.excel.newusers)