Sum of every nth cell in a column
 

I am trying to figure out the formula for adding the nth cell in a given
column. I am using the formula below to add every 4th cell but it is just one
that I found on line. Can someone explain to me the components of this
formula so I can adapt it?

=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))

Sum of every nth cell in a column
 

That is an array formula so it must be committed with Shift + Ctrl + <Enter.
here is how it works...

Moving down through cells C2:C213 it looks at each 2 things. The value of
the cell and the row that cell is on. If the row that cell is on is +1 is
evenly divisible by 1 then it multiplies the value of that cell by 1. If it
is not evenly divisible it multiplies the value by zero.

So Cell C3 is on row 3. 3 + 1 is evenly divisible by 1 so it multiples the
value of the cell by 1. C7 is 7+1 which is divisible...

In the end it just adds the total. Anything on a row not evenly divisible
was multiplied by 0. Anything multipled by 1 is itself.

Row returns the row of the cell.
Mod returns the remainder of the division.
--
HTH...

Jim Thomlinson


"SnackFoodGirl" wrote:

I am trying to figure out the formula for adding the nth cell in a given
column. I am using the formula below to add every 4th cell but it is just one
that I found on line. Can someone explain to me the components of this
formula so I can adapt it?

=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))

Sum of every nth cell in a column
 

Let me try explaining this

The ROW function returns the row number of a single cell reference
ROW(reference)

The MOD function returns the remainder after number is divided by divisor
MOD(number, divisor)

Nested the ROW function in the MOD function which is to supply the number
argument, divided by 2 which is to sum every second cell will produced the
result
of 0

For example, to sum every second cell in the range A1:A20, the formula
could be this

=SUM(IF(MOD(ROW($A$1:$A$20),2=0,$A$1:$A$20,0))

Take note that this is an array formula which you must press Ctrl+Shift+Enter
at the same time to produce the desire result. Excel will add the curly
brackets to the formula

A better option is to use the DSUM function
--
Hope this help

Please click the Yes button below if this post have helped answer your needs

Thank You

cheers, francis





"SnackFoodGirl" wrote:

I am trying to figure out the formula for adding the nth cell in a given
column. I am using the formula below to add every 4th cell but it is just one
that I found on line. Can someone explain to me the components of this
formula so I can adapt it?

=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))

Sum of every nth cell in a column
 

=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))

Not sure why they have that +1 in there. As written that formula would sum
cells starting at C3 so why are they referencing the range starting ar C2?

What cells do you want to sum?

C2, C6, C10, C14, C18, etc.?

--
Biff
Microsoft Excel MVP


"SnackFoodGirl" wrote in message
...
I am trying to figure out the formula for adding the nth cell in a given
column. I am using the formula below to add every 4th cell but it is just
one
that I found on line. Can someone explain to me the components of this
formula so I can adapt it?

=SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0))