Formula Help to Calculate Quarterly Results
 

I need a formula to calculate the quarterly sales of a product based on
the current month plus 2 previous months

Cell A3 indicates the current month formatted as mmm
Column A is the product starting at A5
Columns B - M are the sales by month, starting at B5 for Jan, C5 for
Feb etc

What is the formula I need in cell O5 to give me the quarterly sales
based on the value in cell A3

eg if the value in cell A3 is *Mar* the formula should add the sales in
cell B5:D5, if the value in A3 is changed to *Jun* the formula should
add the sales in cells E5:G5 etc

Thanks in advance for any help


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=564257

Formula Help to Calculate Quarterly Results
 

If A3 is a date formatted as mmm try

=SUM(OFFSET(B5:D5,,MAX(0,MONTH(A3)-3)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564257

Formula Help to Calculate Quarterly Results
 

daddylonglegs Wrote:
If A3 is a date formatted as mmm try

=SUM(OFFSET(B5:D5,,MAX(0,MONTH(A3)-3)))

Thanks daddylonglegs that worked a treat, have a similar thing in
another spreadsheet where the month in the cell is formatted as text eg
JAN, how would the above formula have to be changed to work in that
scenario?


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=564257

Formula Help to Calculate Quarterly Results
 

In that case, assuming the same references as for your previous example
but with months, e.g. "Jan", "Feb" etc. in B4:M4

=SUM(OFFSET(B5:D5,,MAX(0,MATCH(A3,B4:M4,0)-3)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564257

Formula Help to Calculate Quarterly Results
 

daddylonglegs Wrote:
In that case, assuming the same references as for your previous example
but with months, e.g. "Jan", "Feb" etc. in B4:M4

=SUM(OFFSET(B5:D5,,MAX(0,MATCH(A3,B4:M4,0)-3)))

Thanks again


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=564257