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

Mariner

New Member
Joined
Dec 12, 2005
Messages
24
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,001
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 12, 2005
Messages
24
No, wasn't thinking SUMIFS. Much better, agreed. And never used FLOOR before. Mind blown. Outstanding! TY, Jason!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,072
Messages
5,545,825
Members
410,708
Latest member
Rumscheid
Top