How do I SUM every third cell in a row in Excel ?

Tocogroup
Tocogroup used Ask the Experts™
on
Hi,

I have a row of cells in an Excel 2010 worksheet (A2:X2) which I want to total the values and display in cell Y2. However, I only want to total every third cell in the row.

For example, =SUM(C2, F2, I2, L2, O2, R2, U2, X2)

Is there a formula I can use which will only SUM every third cell in a range (row) without my having to enter each individual cell reference, as shown above ?

Thanks
Toco
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may try something like this.....

=SUMPRODUCT((MOD(COLUMN(A2:X2),3)=0)*(A2:X2))

Open in new window

Author

Commented:
That gives me a #VALUE result.

The problem arises when some of the cells (not the cells to be totalled) contain text. Is there a formula which will ignore text in these intervening cells ?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Okay. In that case try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=SUMPRODUCT(IFERROR((MOD(COLUMN(A2:X2),3)=0)*(A2:X2),0))

Open in new window



How to enter an Array Formula:

Remember that an confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.  You will know the array is active when you see curly braces { } appear around your formula.  If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Press F2 on that cell and try again.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
Strangely, it only works if I start in column A. If I change the range in the formula from B2:Y2 then I don't get the desired result.

For example:
=SUMPRODUCT(IFERROR((MOD(COLUMN(B2:Y2),3)=0)*(B2:Y2),0)) gives me a different (and wrong) total

???
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
hmm. Try this more robust Array Formula (Confirm with Ctrl+Shift+Enter)......

=SUMPRODUCT(IFERROR((MOD(COLUMN(B2:Y2)-COLUMN(B2)+1,3)=0)*(B2:Y2),0))

Open in new window

Author

Commented:
Excellent !! Works a treat.
Many thanks. That's a great solution.
Toco
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Toco. Glad it worked as per your expectations. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial