The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates in C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the date of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000A2)

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

Thanks for responding Bernie.
The formula doen't always work: Running the formula on earlier dates seems
to give the right answers, but more recent dates do not. I think it's because
of the dates (or rows) that don't have a RELEASE date yet (blank cell)....
Any help will be appreaciated.
J-LL

"Bernie Deitrick" wrote:

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates in C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the date of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000A2)

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

This will count those where the Release date is not filled in:

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*((ReleasedDates=DateOf Interest) +
(ReleasedDates="")))

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
Thanks for responding Bernie.
The formula doen't always work: Running the formula on earlier dates seems
to give the right answers, but more recent dates do not. I think it's because
of the dates (or rows) that don't have a RELEASE date yet (blank cell)....
Any help will be appreaciated.
J-LL

"Bernie Deitrick" wrote:

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates in
C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the date
of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000A2)

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

Hi Bernie,
Still doesn't work: now I'm getting values in the hundreds when they should
be in the twenties and thirties. I tried moving the parentheses around but
that didn't work either...

"Bernie Deitrick" wrote:

This will count those where the Release date is not filled in:

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*((ReleasedDates=DateOf Interest) +
(ReleasedDates="")))

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
Thanks for responding Bernie.
The formula doen't always work: Running the formula on earlier dates seems
to give the right answers, but more recent dates do not. I think it's because
of the dates (or rows) that don't have a RELEASE date yet (blank cell)....
Any help will be appreaciated.
J-LL

"Bernie Deitrick" wrote:

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates in
C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the date
of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000A2)

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

Are your 'dates' actual dates, or are they text strings that look like dates? That is a common
problem with date values.

Try formatting the cells for number with two decimals. They should change from looking like dates
to looking like numbers if they are actual dates. If they don't change, you could also use

=SUMPRODUCT((DATEVALUE(DeliveryDates)<=DATEVALUE(D ateOf Interest))*
((DATEVALUE(ReleasedDates)=DATEVALUE(DateOf Interest)) + (ReleasedDates="")))

But DATEVALUE will produce an error if any of the values are actual dates.

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
Hi Bernie,
Still doesn't work: now I'm getting values in the hundreds when they should
be in the twenties and thirties. I tried moving the parentheses around but
that didn't work either...

"Bernie Deitrick" wrote:

This will count those where the Release date is not filled in:

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*((ReleasedDates=DateOf Interest) +
(ReleasedDates="")))

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
Thanks for responding Bernie.
The formula doen't always work: Running the formula on earlier dates seems
to give the right answers, but more recent dates do not. I think it's because
of the dates (or rows) that don't have a RELEASE date yet (blank cell)....
Any help will be appreaciated.
J-LL

"Bernie Deitrick" wrote:

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates
in
C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the
date
of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000A2)

HTH,
Bernie
MS Excel MVP


"J-LL" wrote in message
...
The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.