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

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

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
Asked:
Jagwarman
  • 2
1 Solution
 
Glenn RayExcel 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
 
JagwarmanAuthor Commented:
Thanks Glenn Brilliant
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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