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.

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.

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.

--
Biff
Microsoft Excel MVP


"adimar" wrote in message
...

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" 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.

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.


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"adimar" wrote in message
...

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" 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.