How to dealing with functions with 30+ variables?

  • Thread starter Thread starter Darius
  • Start date Start date
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.
 
Try

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

Which will sum every 4th row starting in C4

Mike
 
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
 
Amazingly fast response from Mike H and M Kan both of you hit it right on the
spot and it worked; Thank you both
 
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
 
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.
 
Back
Top