Sumproduct with Match and Vlookup?

[adimar]

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.

 

[T. Valko]

Use this general syntax:

....)*(date_range>=start_date)*(date_range<=end_date)...

 

[adimar]

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]

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.

 

[adimar]

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]

Try this:

D1 = 11/1/2007
E1 = 12/1/2007

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!A1:A50&"backorder",Sheet1!A1:A100&Sheet1!B1:B100,0))),--(Sheet2!B1:B50>=D1),--(Sheet2!B1:B50<=E1))

 

[adimar]

I tried in a few ways and still cannot get it to work. The “backorder†match
won’t work; an item title match is needed instead.

 

[T. Valko]

There's a problem with the range sizes. The range on Sheet1 is longer than
the range on sheet2. Since the dates are on Sheet2 you have to test Sheet2
comapred to Sheet1, not test Sheet1 comapred to Sheet2.

I can't suggest anything else unless I can actually see the file. Is that
possible? If so, let me know how to contact you.

 

[adimar]

This one returns the correct backorder date.
=VLOOKUP(A2,Sheet2!A1:B100,2,FALSE)

I'm still scanning other posts... If I cannot get to include this in a
SUMPRODUCT or INDEX/MATCH (to count all items) I plan to paste the above in a
new column on Sheet1, run the additional step of counting and call it done
for now, optimization later.