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?
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

0
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 ?
0
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

???
0
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

0

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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Toco. Glad it worked as per your expectations. :)
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.