Sumproduct with Match and Vlookup?
I tried in a few ways and still cannot get it to work. The €śbackorder€ť match
wont work; an item title match is needed instead.
"T. Valko" wrote:
Try this:
D1 = 11/1/2007
E1 = 12/1/2007
=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!A1:A50&"backorder",Sheet1!A 1:A100&Sheet1!B1:B100,0))),--(Sheet2!B1:B50=D1),--(Sheet2!B1:B50<=E1))
--
Biff
Microsoft Excel MVP
"adimar" wrote in message
...
Supposing this is the data:
Sheet1
=====
A B
Title1 InStock
Title 2 Backorder
Sheet2
=====
A B
Title2 03/15/08
The formula to count "Number of items backordered with availability date
between 11/1/07 and 12/1/07" is:
sumproduct((Sheet1!B1:B100="Backorder")*((isnumber (match(Sheet1!A1:A100,Sheet2!A1:A50,0)))
and (Sheet2!<<matching dates in B1:B50 for A1:A50
titles=date(2007,11,1))
and (Sheet2!<<matching dates in B1:B50 for A1:A50
titles<=date(2007,12,1)))
I don't know how to write the <matching dates in B1:B50 for A1:A50 titles
part.
Thank you.
"T. Valko" wrote:
Ok, now I'm not following you.
You said:
have Availability date between D1 and D2.
Assume D1 = start_date and D2 = end_date
Then:
.....)*(date_range=D1)*(date_range<=D2)...
date_range is the range on Sheet2 that holds your dates.
--
Biff
Microsoft Excel MVP
"adimar" wrote in message
...
Right. But I don't know how to get a hold of the date.
How do I write "find in Sheet2 a line that matches the given item in
Sheet1
and verify the date in Sheet2 sits between a date range"?
I attempted to write the first part of the question like
(isnumber(match(Sheet1!A1:A100,Sheet2!A1:A50,0))).
How do I write the lookup for the date in Sheet2?
Thank you.
"T. Valko" wrote:
Use this general syntax:
....)*(date_range=start_date)*(date_range<=end_da te)...
--
Biff
Microsoft Excel MVP
"adimar" wrote in message
...
I am looking for a sumproduct formula that counts how many Titles in
a
sheet
are Backordered and have Availability date between D1 and D2.
The data resides in two separate sheets:
Sheet1
=====
Title1 InStock
Title 2 Backorder
Sheet2
=====
Title2 03/15/08
The formula would be something like:
=sumproduct((Sheet1!A1:A100="Backorder")*(isnumber (match(Sheet1!A1:A100,Sheet2!A1:A50,0))).
and here is where I don't know how to further check the date
associated
with
a given item.
Thank you.
|