Adding Quarter to Date using SUM, OFFSET, and MATCH formulas

[Mariner]

Hi,

This should just be an expansion of my YTD calc which is +SUM(OFFSET($AZ8,0,0,1,MATCH(MONTH($LQ$2),$AZ4:$BK4,0))) where AZ8 is January Sales, LQ2 is current month, and AZ4:BK4 is the twelve month array of month numbers, one through twelve. Currently, I have Jan thru March populated. Once April comes in, the width of the offset will expand from 3 columns (Jan - Mar) to 4 (Jan - Apr) and I will then cum four data points.

What I want to do for QTD is sum sales for any given quarter, (e.g., Apr would sum Apr only, May = sum April - May, June = sum Apr - Jun, July = sum Jul only, etc.). Almost have this but need to adjust my reference cell.

I have a 5x12 array with date (1/31/20, 2/29/20, 3/31/20, …), month (1,2,3,4,5,6,7,8,9,10,11,12), quarter (1,1,1,2,2,2,3,3,3,4,4,4), and width of columns that I want summed (1,2,3,1,2,3,1,2,3,1,2,3), and then the corresponding monthly sales figures.

I figured I would mimic the YTD formula above and change my width reference from the month number (1,2,3,4,5,6,7,8,9,10,11,12) to the columns I want summed (1,2,3,1,2,3,1,2,3,1,2,3). Now I need to change the reference cell from January Sales (AZ8) to sales in either of January, April, July, or September depending on the corresponding quarter. Getting bogged down with INDEX and MATCH logic and was hoping someone had a quick fix. Not interested in running a pivot table or using a macro.

Thanks in advance!

 

[jasonb75]

Any reason behind the choice of functions for your formulas? There are alternatives that would be far more efficient.

Month formula

=SUMIFS($AZ8:$BK8,$AZ4:$BK4,"<="&MONTH($LQ$2))

QTR formula

=SUMIFS($AZ8:$BK8,$AZ4:$BK4,"<="&MONTH($LQ$2),$AZ4:$BK4,">"&FLOOR(MONTH($LQ$2)-1,3))

 

[Mariner]

No, wasn't thinking SUMIFS. Much better, agreed. And never used FLOOR before. Mind blown. Outstanding! TY, Jason!