Sum every third column in a row

Carl B

Board Regular
Joined
Feb 15, 2002
Messages
65
Is there a nifty formula that would allow me to sum every third cell in a range?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
See if this does it; thanks to Aladin for assisting:

=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67)-ROW(C10),3)=0))
 

Carl B

Board Regular
Joined
Feb 15, 2002
Messages
65

ADVERTISEMENT

Thank you Tom and Aladdin,
I kind of mislead you in the range that I was looking for, it should of been something along the lines of C10:AL10. Very intersting formula, I believe I can modify it to work for me. Thanks again for pointing me in the right direction.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-09-26 20:56, Carl B wrote:
C10:C67

=SUMPRODUCT((MOD(ROW(C10:C67)-CELL("Row",$C10)+H1,H2)=0)*(C10:C67))

where H1 houses the index that indicates the value to start with from the set of values of interest and H2 the index that indicates the Nth value to sum.

If you want to sum beginning with the first value, set H1 to 0. Since you want to sum every 3rd value, set H2 to 3.

Tom Urtis/Juan Pablo: This formula in fact reduces 4 different formulas to just one with 2 parameters (see Pearson's SUMEVERY.XLS). It's independent of the native row/column number. I remember attempting such a formula at the old board in an exchange with Juan but failed to succeed. I had promised myself to dedicate such a formula to Juan if I could build it.

Aladin
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-26 21:46, Carl B wrote:
Thanks again, I changed ROW to COLUMN and it works perfectly!

It won't if you insert columns before your data... Use instead:

=SUMPRODUCT((MOD(COLUMN(C10:AL10)-CELL("Col",$C10)+H1,H2)=0)*(C10:AL10))

See my other post in this thread for H1 and H2 (which I suppose to be 0 and 3 respectively in your case).

Aladin
This message was edited by Aladin Akyurek on 2002-09-26 22:01
 

Myproblem

Board Regular
Joined
May 24, 2010
Messages
198
hi guys, i am new here

i have problem with summing every 7th number in 10 digit number, like this:

A B
1000004000 100
1000005000 50
...
...

i have in column A like above number with 10 digit number, and in column B specific values.
i need formula, it would be great just one:) to sum all values in column B but with criteria that formula summin every 7th, example 7th number is 4; number in number in column A

thx
 

Forum statistics

Threads
1,144,312
Messages
5,723,649
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top