lookup table
Posted by mcarter973 on August 09, 2001 11:26 AM
I'm using a table to lookup values on a monthly basis.
column A: January, February, March, etc.
column B: Quantity (cell B13 is the YTD figure).
I want to create a formula that will lookup the month in column A and sum the YTD numbers in column B.
For example:
January 8
February 10
March 23
April 17
YTD 58
If I look up February I want to sum just January and February (8 and 10).
Thanks
Posted by Aladin Akyurek on August 09, 2001 11:38 AM
How did you enter the dates? Just as you typed? If so, what is its format?
Aladin
Posted by Mark W. on August 09, 2001 11:40 AM
=SUM($C$8:OFFSET($B$7,MATCH("February",$B$8:$B$11,0),1))
Posted by mcarter973 on August 09, 2001 11:41 AM
They are month-end dates (01/31/01,etc).
Posted by Mark W. on August 09, 2001 11:44 AM
It doesn't really matter what the dates in your
list are... just use: =SUM($C$8:OFFSET($B$7,2,1)) They are month-end dates (01/31/01,etc). : How did you enter the dates? Just as you typed? If so, what is its format? : Aladin :
Posted by Aladin Akyurek on August 09, 2001 11:46 AM
I'll assume also a date-formatted date in say C1, your lookup value.
In B13 enter: =SUMPRODUCT((MONTH(A1:A12)=MONTH(C1))*(B1:B12))
Aladin
========== They are month-end dates (01/31/01,etc). : How did you enter the dates? Just as you typed? If so, what is its format? : Aladin :
Posted by Aladin Akyurek on August 09, 2001 11:49 AM
Correction
Posted by Mark W. on August 09, 2001 11:51 AM
If your date's in C1...
...as Aladin assumed then...
=SUM($C$8:OFFSET($B$7,MONTH(C1),1))
...will do the trick. There's no need for a
lot of intermediate calculations. Just "jump to
the spot" and sum!!!
Posted by mcarter973 on August 09, 2001 12:06 PM
Re: Correction
Aladin -
It didn't work - is this a CSE formula? =SUMPRODUCT((MONTH(A1:A12)<=MONTH(C1))*(B1:B12))
Posted by Aladin Akyurek on August 09, 2001 12:21 PM
Re: Correction
No, it is not.
The formula assumes a date in C1 (criterion date), dates in column A (which need not to be sorted), and with these dates corresponding numbers in column B.
If the month February is the criterion, =MONTH(C1) must return 2. Also, of course February dates in A must return 2 when MONTH applied to each of them. Is this perhaps the reason why the formula does not work?
Aladin
