[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 342

# Excel formula

is there a formula that will add like this?

columns
A       B       C       D       E       F       G       H       i
10     20     30     40      50    60     70      80     90

and this can keep going on to the right to AN
So in AN I want to total A,D,G etc etc
in AO I want to total B,E,H etc
and in AP I want to total C,F,I etc.

Possible?

Thanks
0
Jagwarman
• 2
1 Solution

Excel VBA DeveloperCommented:
Since you're adding every third column, one can use the MOD function in combination with a SUMPRODUCT to get these results.

Assuming your original data is in cells A2:AM2, insert this formula in cell AN2 and copy to the right two cells:
=SUMPRODUCT(--(MOD(COLUMN(\$A\$2:\$AM\$2,3)=MOD(COLUMN(),3)),\$A\$2:\$AM\$2)

or, a little more transparent:
AN2: =SUMPRODUCT(--(MOD(COLUMN(\$A\$2:\$AM\$2),3)=1),\$A\$2:\$AM\$2)
AO2: =SUMPRODUCT(--(MOD(COLUMN(\$A\$2:\$AM\$2),3)=2),\$A\$2:\$AM\$2)
AP2: =SUMPRODUCT(--(MOD(COLUMN(\$A\$2:\$AM\$2),3)=0),\$A\$2:\$AM\$2)

Regards,
-Glenn
0

Author Commented:
Thanks Glenn Brilliant
0

Excel VBA DeveloperCommented:
You're welcome.
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.