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€�)*((isnu mber(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 dont 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.