| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to dealing with functions with 30+ variables?
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
Try
=SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000) Which will sum every 4th row starting in C4 Mike "Darius" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
Here is something similar
http://www.kan.org/tips/excel_sumproduct_advanced3.php Not entirely sure where your data reside in the sheet, but you could add a helper row and then use the function MOD(helper cell, 4)=0 as one of the conditions in your SUMPRODUCT -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Darius" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
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 -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Darius" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
Amazingly fast response from Mike H and M Kan both of you hit it right on the
spot and it worked; Thank you both "Mike H" wrote: Try =SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000) Which will sum every 4th row starting in C4 Mike "Darius" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
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 On Thu, 24 Jul 2008 12:05:01 -0700, Darius wrote: Amazingly fast response from Mike H and M Kan both of you hit it right on the spot and it worked; Thank you both "Mike H" wrote: Try =SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000) Which will sum every 4th row starting in C4 Mike "Darius" wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
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. "Gord Dibben" wrote: 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 On Thu, 24 Jul 2008 12:05:01 -0700, Darius wrote: Amazingly fast response from Mike H and M Kan both of you hit it right on the spot and it worked; Thank you both "Mike H" wrote: Try =SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000) Which will sum every 4th row starting in C4 Mike "Darius" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to dealing with functions with 30+ variables?
OK thanks
Now I know why the formulas from Mike and M Kan work for you. Gord On Thu, 24 Jul 2008 15:34:03 -0700, Darius wrote: 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. "Gord Dibben" wrote: 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 On Thu, 24 Jul 2008 12:05:01 -0700, Darius wrote: Amazingly fast response from Mike H and M Kan both of you hit it right on the spot and it worked; Thank you both "Mike H" wrote: Try =SUMPRODUCT(--(MOD(ROW(C4:C1000),4)=0),C4:C1000) Which will sum every 4th row starting in C4 Mike "Darius" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Confused about setting up functions based on variables? | Excel Worksheet Functions | |||
| Functions/Formulas to count multiple variables | Excel Worksheet Functions | |||
| constructing (complex) variables with worksheet functions | Excel Discussion (Misc queries) | |||
| LOGICAL FUNCTIONS WITH MULTIPLE VARIABLES | Excel Worksheet Functions | |||
| Can I use variables for workheet name references in Excel functions? | Excel Discussion (Misc queries) | |||
Linear Mode
