Sum Every other row

**nkissam** · 05-13-2007, 09:55 PM

Is there a way to write a formula in Excel that will sum cells in every third row?

**duane** · 05-13-2007, 10:06 PM

yes..........for example for summing rows 1, 4, 7, etc through row 20

=SUM(IF(MOD(ROW(A1:A20),3)=1,A1:A20))

entered with control+shift+enter

**nkissam** · 05-13-2007, 10:10 PM

Just wondering. I am not familiar with this command in Excel. What does it do? Also, I am working on a MAC - does that influence use of this command?

thanks for your help

Nicole

**duane** · 05-13-2007, 10:14 PM

the mod function returns the remainder after the the first arguement is divided by the 2nd The row function returns the row number.

So, row(a3) returns 3 and
mod(row(a3),3) returns 0 (3/3 = 1 with no remainder

you can evaluate a range of rows (an array)

so, the formula determines if the mod, 3 of the row is 1 (for example rows 1, 4, 7, etc), and sums only those rows, ignoring the rest

**duane** · 05-13-2007, 10:18 PM

oh, on the Mac question, I do not know, but I would assume that Excel on Macs can handle arrays

**nkissam** · 05-13-2007, 11:31 PM

I have rows C9:C100, I want to add rows, C9, C14, C19, C24, C29... etc. until I get through row C100. So, I want to add every 5th or 6th (not sure which) row, starting with C9.

Do we still have the right formula

**nkissam** · 05-13-2007, 11:58 PM

I understand the formula now. Thanks,

However, there are a number of blank rows (every 6th row) between C9 and C100.

Will this mess the formula up? I am getting a strange total.