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€�)*(isnu mber(match(Sheet1!A1:A100,Sheet2!A1:A50,0)))€¦
and here is where I dont know how to further check the date associated with
a given item.

Thank you.

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.

Right. But I dont 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.

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.

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.

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.