Excel Formula to total amount quarterly in date row

0
votes

I am trying to calculate the difference between total current quarter and total previous quarter in Excel. The data is at the row title and the quarter month is not standard. For example January is consider as quarter 4 and February is quarter 1. Also date can change from one report to the next.

This is the sample data: enter image description here

I want to add a formula in column H that total all amount in Q4 (Nov-Jan) and in column I the total amount in Q1 (Aug-Oct) for every row. Next month report column G will be February and I need a formula that dynamic enough to know the current quarter amount to be placed in column H.

I was thinking about using sumproduct and choose but Excel does not like it:

=Sumproduct(--((CHOOSE(MONTH(B29:G29),4,1,1,1,2,2,2,3,3,3,4,4)=4),B30:C30))

If this formula can work, I can just copy paste it to the remaining row in column H and I.

2
excelexcel-formulavba
I think you were trying to use {=SUMPRODUCT(--(CHOOSE(MONTH($B$29:$G$29),4,1,1,1,2,2,2,3,3,3,4,4)=4),$B30:$G30)} for that formula, i.e. G30 instead of C30, and removing the extra bracket before CHOOSE and after B30:C30, and making it an array formula (and making certain references absolute rather than relative) – YowE3K

2 Answers

0
votes

With this array formula at H30 would do what you want and you can copy paste in I30 and in the rows down. It will work also if you add columns for Q2 and Q3.

H30 -->
 =SUM(IF(CHOOSE(MONTH($B$29:$G$29),4,1,1,1,2,2,2,3,3,3,4,4)=INT(RIGHT(H$29,1)),$B30:$G30))
  Ctrl+Shift+Enter

Notice it takes the quartile's number from the row above, it fixes the columns and parametrizes with the row.

0
votes

Based on above example use:

=SUMPRODUCT(--(1+MOD(INT((MONTH($B$29:$G$29)-2)/3),4)=RIGHT(H$29,1)*1),$B30:$G30)