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

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
TocogroupAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this.....

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

Open in new window

TocogroupAuthor 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 ExpertCommented:
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.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Read On!
TocogroupAuthor 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

???
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Excellent !! Works a treat.
Many thanks. That's a great solution.
Toco
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Toco. Glad it worked as per your expectations. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.