Search code examples
excelif-statementexcel-formulasumsumifs

Excel formula - Get the sum of each variable every 6 days

enter image description here

I have the following dataset where I want to get the sum of each variable every 6 days. I can get the total sum of every 6 days using

=SUM(OFFSET($A$2,,(COLUMNS($A$5:A5)-1)*6,,6))

And I can get the total sum of each variable using

=SUMIF(A1:S1,A1,A2:S2)

But I cant get the total sum of each variable within the block of 6 days. It won't increment when I drag the formula. So the results should be

      First batch      Second batch         Third batch
A      B       C      A      B       C      A      B       C
2      2       2      4      4       4      6      6       6
Solution

  • You can use SUMPRODUCT:

    =SUMPRODUCT((1:1=A6)*2:2*(COLUMN(1:1)>(INT((COLUMN()-1)/3)*6))*(COLUMN(1:1)<=(INT((COLUMN()-1)/3+1)*6)))

    enter image description here

    Edit:

    To shift the column by five position, you will need to change the following parameters in the formula:

    1. Full row range change to exact range, i.e. 1:1 to e.g. $F$1:$W$1
    2. Change COLUMN()-1 to COLUMN()-3

    If you also want to change the number of columns to be summed, additionally replace the factor of 6 with a 7-1 for seven columns or 36-30 for thirty-six columns.

    So formulas looks like:

    batch of 6 cols

    =SUMPRODUCT(($F$1:$W$1=F6)*$F$2:$W$2*(COLUMN($F$1:$W$1)>=((INT((COLUMN()-3)/3))*6))*(COLUMN($F$1:$W$1)<((INT((COLUMN()-3)/3+1))*6)))

    batch of 7 cols

    =SUMPRODUCT(($F$1:$Z$1=F6)*$F$2:$Z$2*(COLUMN($F$1:$Z$1)>=((INT((COLUMN()-3)/3))*7-1))*(COLUMN($F$1:$Z$1)<((INT((COLUMN()-3)/3+1))*7-1)))

    batch of 36 cols

    =SUMPRODUCT(($F$1:$WW$1=F6)*$F$2:$WW$2*(COLUMN($F$1:$WW$1)>=((INT((COLUMN()-3)/3))*36-30))*(COLUMN($F$1:$WW$1)<((INT((COLUMN()-3)/3+1))*36-30)))