CODEWITHSUNDEEP
excelexcel-formulaexcel-2016sumproduct
Sarah Highsmith
Sarah Highsmith

Reputation: 13

Sum of every 4th column in a row in Excel to end of row

I have a VBA Code that adds 4 new columns (Quantity Used, Scrapped or no Bill, Invoiced, Revenue) every time a new sheet is added in a workbook. I am needing to create a formula that adds every 4th column together starting at column S to the end of the row as the end column is always changing. Below is the formula I have created, but I get #VALUE!. Can someone please help. Below is a link to the sample worksheet.

=SUMPRODUCT((MOD(COLUMN($S13:$XN13)-0,4)=0)*($S13:$XN13))

https://drive.google.com/file/d/1m_aZJyUdShydn53Puvb2G38g5C1YCvCJ/view?usp=sharing

Upvotes: 1

Views: 273

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Maybe something like this (array formula, so Ctrl + Shift + Enter):

=SUM(IF(MOD(COLUMN($S$13:$XN$13),4)=0,$S$13:XN13,""))

I couldn't change your array formula, therefore in the picture I took row 71.

enter image description here

This formula works, but it assumes that you have no empty cells in the row you are checking (array formula, so Ctrl + Shift + Enter).

=SUMPRODUCT((MOD(COLUMN($S13:$XN13)-0,4)=0)*($S13:$XN13))

enter image description here

Both formulas assumes that you don't have any #VALUE or #N/A values in your week data (i.e. no error values in range $S13:$XN13).

For other columns, change the number of the cluster for the MOD, explanation.

So to SUM Column G, we change the =0 to =3:

=SUM(IF(MOD(COLUMN($S$13:$XN$13),4)=3,$S$13:XN13,""))

Column H

=SUM(IF(MOD(COLUMN($S$13:$XN$13),4)=1,$S$13:XN13,""))

Column J

=SUM(IF(MOD(COLUMN($S$71:$XN$71),4)=2,$S$71:XN71,""))

Upvotes: 0

Related Questions