CODEWITHSUNDEEP
excelif-statementexcel-formulasumsumifs
cms72
cms72

Reputation: 177

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

Upvotes: 0

Views: 233

Answers (2)

basic
basic

Reputation: 11968

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)))

Upvotes: 1

teylyn
teylyn

Reputation: 35970

Instead of creating a really, really, really complex formula that can be dragged right, I suggest you add a row to the data at the top that identifies the batch number. Then you can use that batch number as an additional parameter in the Sumifs(). you can hide the rows with the batch numbers if they upset your spreadsheet design.

=SUMIFS(3:3,1:1,A16,2:2,A17)

enter image description here

This is far easier than creating a formula that dynamically adjusts references in tiered steps of three and six.

Upvotes: 1

Related Questions