Summing every third cell in a range

Ajtoo · Mar 21st, 2002, 08:52 AM

Is it possible to sum every third cell in a column?
- where the number of entries is variable, and
- ignoring cells with text in them

Tom Schreiner · Mar 21st, 2002, 08:58 AM

Yes

Are you familiar with macros???

Mark W. · Mar 21st, 2002, 09:10 AM

If A2:A11 contains...

{10;20;30;4;5;"dog";12;11;7;6}

...the array formula...

{=SUM(IF(MOD(ROW(A2:A11)-ROW(A2)+1,3),,A2:A11))}

...produces the value 37.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

Dave Patton · Mar 21st, 2002, 09:21 AM

An array formula to sum every 3rd.
Enter it with Ctrl-Shift-Enter.

For ease of use:

The first amount to be summed should be in a row that is divisible by the Nth factor.
Example Row 9/3

One can insert or delete row to accommodate this.

=SUM(IF(MOD(ROW(A1:A17),3)=0,A1:A17))

or more generic
Nth 3 or alternative
rD range of numbers
=IF(Nth=0,0,SUM(IF(MOD(ROW(rD),Nth)=0,rD,0)))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 08:52 AM	#1
Ajtoo New Member Join Date: Mar 2002 Location: London Posts: 5	Is it possible to sum every third cell in a column? - where the number of entries is variable, and - ignoring cells with text in them

Mar 21st, 2002, 08:58 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Yes Are you familiar with macros???

Mar 21st, 2002, 09:10 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	If A2:A11 contains... {10;20;30;4;5;"dog";12;11;7;6} ...the array formula... {=SUM(IF(MOD(ROW(A2:A11)-ROW(A2)+1,3),,A2:A11))} ...produces the value 37. Note: Array formulas must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

Mar 21st, 2002, 09:21 AM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	An array formula to sum every 3rd. Enter it with Ctrl-Shift-Enter. For ease of use: The first amount to be summed should be in a row that is divisible by the Nth factor. Example Row 9/3 One can insert or delete row to accommodate this. =SUM(IF(MOD(ROW(A1:A17),3)=0,A1:A17)) or more generic Nth 3 or alternative rD range of numbers =IF(Nth=0,0,SUM(IF(MOD(ROW(rD),Nth)=0,rD,0)))