Google-sheets – How to SUM based on fiscal quarter

google sheets

My monthly spreadsheet data looks like this:

+----------+----------+
|   Date   | Sessions |
+----------+----------+
| Nov 2017 |     6289 |
| Oct 2017 |     8065 |
| Sep 2017 |     7805 |
| Aug 2017 |    11175 |
| Jul 2017 |     7068 |
| Jun 2017 |     7745 |
| May 2017 |     9406 |
| Apr 2017 |     8179 |
| Mar 2017 |     9423 |
| Feb 2017 |    10897 |
| Jan 2017 |    11488 |
| Dec 2016 |     7198 |
| Nov 2016 |    11154 |
| Oct 2016 |    10608 |
| Sep 2016 |     9094 |
| Aug 2016 |    10568 |
| Jul 2016 |     8619 |
| Jun 2016 |     9593 |
| May 2016 |     8282 |
| Apr 2016 |     9116 |
| Mar 2016 |     8455 |
| Feb 2016 |     8731 |
| Jan 2016 |     8242 |
+----------+----------+

The information will update to always show the current month as the top row.

Fiscal year is Apr 1–Mar 31, so we are in currently in Q3.

I ultimately need to generate values for date periods such as:

QTD, QTD Last Year, Last Quarter, Two Quarters Ago, and Last Quarter of Last Year.

I've managed to create a cell that gives me a value identifying the row as "Q1 2017" for example, but this still doesn't help me express concepts like Two Quarters Ago or Last Quarter of Last Year.

The simplest solution I can imagine—but don't know how to create—would be to create an "index" value for each quarter, e.g.:

+----------+----------+---------------+
|   Date   | Sessions | Quarter Index |
+----------+----------+---------------+
| Nov 2017 |     6289 |             0 |
| Oct 2017 |     8065 |             0 |
| Sep 2017 |     7805 |             1 |
| Aug 2017 |    11175 |             1 |
| Jul 2017 |     7068 |             1 |
| Jun 2017 |     7745 |             2 |
| May 2017 |     9406 |             2 |
| Apr 2017 |     8179 |             2 |
| Mar 2017 |     9423 |             3 |
| Feb 2017 |    10897 |             3 |
| Jan 2017 |    11488 |             3 |
| Dec 2016 |     7198 |             4 |
| Nov 2016 |    11154 |             4 |
| Oct 2016 |    10608 |             4 |
| Sep 2016 |     9094 |             5 |
| Aug 2016 |    10568 |             5 |
| Jul 2016 |     8619 |             5 |
| Jun 2016 |     9593 |             6 |
| May 2016 |     8282 |             6 |
| Apr 2016 |     9116 |             6 |
| Mar 2016 |     8455 |             7 |
| Feb 2016 |     8731 |             7 |
| Jan 2016 |     8242 |             7 |
+----------+----------+---------------+

Then I could simply SUMIF based on an index value of 1 for example (ie Last Quarter), etc.

Best Answer

A single-cell formula that can be copied down the column could be this:

=4*(year(A$3) - year(A3)) + ceiling(month(A$3)/3) - ceiling(month(A3)/3)

Here A$3 is the absolute reference to the top row with data, and A3 is relative, which becomes A4, A5, etc down the column. Your quarters are listed with the most recent on top, which is numbered 0; previous quarters are numbered 1, 2, ...

Explanation: a quarter is identified by taking the month of the current date, dividing it by 3, and rounding up; this way, the months 1,2,3,4,5,6,7,8,9,10,11,12 become quarters 1,1,1,2,2,2,3,3,3,4,4,4. But we must also include the year, so take the difference of years and multiply it by 4.

However, when the time comes to insert a new quarter you will find the previous approach inconvenient: the formulas will be pushed down and refer to A$4, ignoring the added quarter. This can be avoided with arrayformula, placed one row higher than the data; say in D2:

={"Quarter Index"; arrayformula(
   4 * (year(indirect("A3")) - year(indirect("A3:A")))
   + ceiling(month(indirect("A3"))/3) - ceiling(month(indirect("A3:A"))/3)
 )}

(linebreaks for readability, optional). This puts "Quarter Index" in D2, and then computes the indices for every quarter in A3:A. The cell references are made via indirect, so they will not be affected by any insertion or deletion of data rows. And the formula, being placed above the data (in row 2) will not be shifted down by row insertion above row 3.

Related Topic