asked on 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
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
Microsoft Excel
Last Comment
Subodh Tiwari (Neeraj)
ASKER
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 ?
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 ?
Okay. In that case try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
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.
=SUMPRODUCT(IFERROR((MOD(COLUMN(A2:X2),3)=0)*(A2:X2),0))
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.
ASKER
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(C
???
For example:
=SUMPRODUCT(IFERROR((MOD(C
???
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Excellent !! Works a treat.
Many thanks. That's a great solution.
Toco
Many thanks. That's a great solution.
Toco
You're welcome Toco. Glad it worked as per your expectations. :)
Open in new window