Formula Every 3 Line

D

David

I need to pick a cell every 3 line and have them total , and I have 1200
lines, so picking each one would be very trying, is there a easy to do this.
The 2 lines between also have values, but I do not want to total them.
 
D

David Billigmeier

What do you mean by "Total", do you mean just Sum? If so, let's say your
data is in the range A1:A1200 and you want to sum every 3rd line, starting on
row 3, you can use this function, entered CTRL+SHIFT+ENTER:

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

Change to fit your data.
 
D

David Billigmeier

Just noticed a couple typo's in the previous formula, here is a new one
(entered CTRL+SHIFT+ENTER):

=SUM(IF(MOD(ROW(A1:A1200),3)=0,A1:A1200,""))
 
D

Domenic

To sum every 3rd row beginning with the first cell in the range, try...

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),A1:A100)

To sum every 3rd row beginning with the first occurrence of '3rd',
change '+0' to '+1'.

Hope this helps!
 
D

David

Thank you that did it.

Domenic said:
To sum every 3rd row beginning with the first cell in the range, try...

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),A1:A100)

To sum every 3rd row beginning with the first occurrence of '3rd',
change '+0' to '+1'.

Hope this helps!
Click to expand...
 
Top