Using a VLookup "type" reference but the result is the CELL not th

[Guest]

I'm using a VLOOKUP to help me select a date range from another work sheet.

ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10.

Any help would be greatly appreciated

 

[Frank Kabel]

Hi
try the following
=SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)),B1:
B100)

 

[Aladin Akyurek]

It looks like the revenue data is sorted on dates in ascending order. If so:

Let B2:C100 on Sheet2 house the revenue data with dates in B2:B100.

Let A2 on Sheet1 house 6/01/04, the first date condition.
Let B2 on Sheet1 house 6/30/04, the second date condition.

In C2 on Sheet1 enter:

=SUM(INDEX(Sheet2!$C$2:$C$100,MATCH(A2,Sheet2!$B$2:$B$100)+(LOOKUP(A2,Sheet2
!$B$2:$B$100)<>A2)):INDEX(Sheet2!$C$2:$C$100,MATCH(B2,Sheet2!$B$2:$B$100,1))
)

I'm using a VLOOKUP to help me select a date range from another work sheet.

ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding

revenue number. The same is applied to 6/30/04. I'm trying to sum the
revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the
VLOOKUP only returns the value and not the cell location. I need the cell
locations to sum the revenue numbers in between. ie. the revenue numbers
between B4 and B10.