Sumproduct with Match and Vlookup?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adimar - 05 Feb 2008 03:36 GMT

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.

Reply to this Message

T. Valko - 05 Feb 2008 04:01 GMT

Use this general syntax:

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

Signature

Biff
Microsoft Excel MVP

>I am looking for a sumproduct formula that counts how many Titles in a
>sheet
[quoted text clipped - 17 lines]
>
> Thank you.

Reply to this Message

adimar - 05 Feb 2008 04:21 GMT

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.

> Use this general syntax:
>
[quoted text clipped - 21 lines]
> >
> > Thank you.

Reply to this Message

T. Valko - 05 Feb 2008 04:46 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Right. But I don't know how to get a hold of the date.
>
[quoted text clipped - 34 lines]
>> >
>> > Thank you.

Reply to this Message

adimar - 05 Feb 2008 16:05 GMT

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.

> Ok, now I'm not following you.
>
[quoted text clipped - 48 lines]
> >> >
> >> > Thank you.

Reply to this Message

T. Valko - 05 Feb 2008 18:08 GMT

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))

Signature

Biff
Microsoft Excel MVP

> Supposing this is the data:
>
[quoted text clipped - 76 lines]
>> >> >
>> >> > Thank you.

Reply to this Message

adimar - 05 Feb 2008 19:55 GMT

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.

> Try this:
>
[quoted text clipped - 83 lines]
> >> >> >
> >> >> > Thank you.

Reply to this Message

T. Valko - 05 Feb 2008 20:01 GMT

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.

Signature

Biff
Microsoft Excel MVP

> I tried in a few ways and still cannot get it to work. The "backorder"
> match
[quoted text clipped - 90 lines]
>> >> >> >
>> >> >> > Thank you.

Reply to this Message

adimar - 05 Feb 2008 23:09 GMT

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.

> 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
[quoted text clipped - 97 lines]
> >> >> >> >
> >> >> >> > Thank you.

Reply to this Message