How to dealing with functions with 30+ variables?

D

Darius

I have a worksheet that has over 2000 rows and every 4th row I have a number
that I am trying to sum up. Currently I understand that I can not have more
then 30 variables in a function so I take 30 rows at a time then sum up the
results and adding them up. Here is what I am using now as a formula:
=SUM(C4,C8,C12,C16,C20,C24,C47,C51,C55,C59,C63,C67,C28,C32,C37,C71,C75,C79,C83,C87,C93,C97,C101,C105,C109,C113,C117,C121,C125,C129)
I take that result and added to the rest for ever 30 rows

The worksheet is getting bigger by the day and I need something that adds
every 4th row no matter how many rows I have. All the help will be greatly
appreciated. Thank you.
 
M

Mike H

Try

=SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000)

Which will sum every 4th row starting in C4

Mike
 
M

M Kan

You can also use the ROW function with MOD to avoid the need for a helper
column. The formula would look like:

=MOD(ROW(E39)-38,4)

assuming my data set started at row 39 for example
 
D

Darius

Amazingly fast response from Mike H and M Kan both of you hit it right on the
spot and it worked; Thank you both
 
G

Gord Dibben

Just curious

Your original formula was quite inconsistent with summing every fourth row
in Column C

Was that just an example formula?


Gord Dibben MS Excel MVP
 
D

Darius

The reason why it was so inconsistent is because every few rows the person
would sum it up and added to a total which in turn it will be summed and
added to the rest of the totals and that is how they were able to sum up
3012/4 = 753 rows which is still growing.
 
Top