HLOOKUP, then VLOOKUP, then COUNT

[max1x]

I have a list of 3 columns

PROD  SD         ED
APP   1-1-2010   1-31-2010
SW    1-1-2010   1-11-2010

Lookup data is in the following format

DATE     APP  SW
1-1-2010  1    3
1-2-2010  1    1
.
.
1-31-2010 8    0

I can't get the formula right to do:
First check if the PROD is listed in the LookUp row, if Yes, then between the SD and ED date range sum the values for each prod and list it.
I'm using COUNTIF(IF(ISNA(HOOKUP...), but it's not working.

 

[xlhelp]

A little more info would be so good. Are you doing HLOOKUP in the 3 columns? And you wish to sum the data table? or the opposite?

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[SkipVought]

Hi,

Your two examples are not consistent!

Assuming that you use Named Ranges for each column as per column headings and that your 'lookup data' begins in G1, then...
[tt]
H2: =SUMPRODUCT((SD<=$G2)*(ED>=$G2)*(PROD=H$1))
[/tt]
copy formula across and down.

My results using your example...
[tt]
DATE APP SW
1/1/2010 1 1
1/2/2010 1 1
1/31/2010 1 0
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

The data is defined in two tabs. tab1 has product and data ranges. The second sheet captures the instances of each product for every day, i.e to say

on 1-1-2010 there was 1 inst of APP and 3 inst of SW
on 1-2-2010 there was 1 inst of APP and 1 inst of SW

The list goes on for the entier year.

The goal is caputre the SUM of instance that transpired between the date range for each product type.

so if the date range is 10-1-2010 and 10-2-2010, then
APP = 2
SW = 4

 

[SkipVought]

on 1-1-2010 there was 1 inst of APP and 3 inst of SW

Not in your example!

Post an example where there are 3 instances of SW in 1/1/2010.

Did you try my solution?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

I think there is some confusion going on here. Max1x, can you confirm that both of your tables in your first post are to be used as input into a formula .... and that in fact you want the formula to SUM and not COUNT figures in your tables?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

The OP explicitly stated, "...sum the values for each prod...", however, the posted example had NO values to sum; merely a product values and dates!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

It seems to me that the posted example shows products with start and end dates, and then a data area from which summing is required for each particular product within the date range. That's what I understand is being asked. I may be wrong.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

I read that the RESULT was in the second posting.

It is ALL not very clear.

OP, please post your examples AND a detail description of what results you want, related to the posted values, clearly, consicely and completely.

Please keep in mind that contributors have regular jobs that consume 90+% of their time in addition to browsing dozens of Tek-Tip threads and answering other posters' questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

The first tab has the PROD and date range
The second tab thas the DATE LIST and instance of each product.

From tab 1:
I need to take PROD, SD and ED and then

In tab 2:
Lookup PROD (in the row) and then for the date range (in column) take the instances and sum them up.

For APP between 1-1-10 thru 1-31-10, based on example above could (1+1+8) = 10

If I did the same for SW, for the above date range (3+1+0) = 4

Thank you everyone for you help on this.

 

[SkipVought]

I renamed your heading on sheet2 as DTE as DATE is a reserve word in Excel and most systems.

Here is the formula I used in D2 on sheet1, assuming that your table begins in A1...
[tt]
D2: =SUMPRODUCT((INDIRECT(A2)*(DTE>=B2)*(DTE<=C2)))
[/tt]
My Results...
[tt]
PROD SD ED ColD
APP 1/1/2010 1/31/2010 10
SW 1/1/2010 1/11/2010 4
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

I'm getting a #REF! as a result. Working on it and trying to understand what the requirements are for INDIRECT and SUMPRODUCT.

 

[SkipVought]

You must use NAMED RANGES. faq68-1331



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

I did a step through of the formula.

First step INDIRECT(A2) evaluates to APP
Sec step APP then reports as #REF! and date evaluates correctyly
Third step #REF!,Number and then Date
Final outcome
#REF! * number * number.

I've tried changing the PRODC col formatting to General, text..etcas some products are alpa-numareic, example DISK2. Still getting the same results.

 

[SkipVought]

You mean A2 evaluates to APP and INDIRECT(APP) evaluates to #ref!

DID YOU NAME YOUR RANGES using Insert > Name > Create > Create Names in TOP row????

Can you find ALL the Names in the Name Box, to the LEFT of the Formula Bar???

if not, do it!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[max1x]

I actually typed the name of the col in the left of formula bar and when I do a Insert->Name->Define, the names are listed. Still working on it.

 

[SkipVought]

I actually typed the name of the col in the left of formula bar...

JUST haveing the NAME defined is not enough!

The Name MUST refer to the correct RANGE!!!!!!

Select ALL data including the headings.

Then Insert > Name > Create -- Create names in TOP row

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}