| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of more than 30 non-adjacent cells
I have a column with hundreds of populated cells, and I would like to add up
the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
=SUM(A1,B1)+SUM(D1:F1)....etc
-- Kind regards, Niek Otten Microsoft MVP - Excel "Picman" wrote in message ... I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Are the cells at regularly spaced intervals?
If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Yes they are at regularly spaced intervals.
"John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Regularly spaced intervals is easy enough to do:
Suppose your range goes through row 100 start = your starting row within the range, so say the first number you want summed is in row 11, you would substitute 11 for the 2 appearances of start in the formula, likewise, you would substitute for nth however many rows is the regular interval. =SUMPRODUCT(--(MOD(ROW(A1:A100)-start,nth)=0),--(ROW(A1:A100)=start),A1:A100) If this doesn't seem to work for you, post back with column reference, starting point, how many rows, etc. -- ** John C ** "Picman" wrote: Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
It is always helpful to tell us what you have (in this case, the starting
row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Actually, we can eliminate one of the function calls...
=SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
I seem to be missing something because I get an error.
My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
=SUMPRODUCT(--(MOD(ROW(A1:A100)-start,nth)=0),--(ROW(A1:A100)=start),A1:A100)
changes to =SUMPRODUCT(--(MOD(ROW(E1:E1000)-3,4)=0),--(ROW(E1:E1000)=3),E1:E1000) is my formula modified -- ** John C ** "Picman" wrote: I seem to be missing something because I get an error. My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Substituting your conditions into this general format...
=SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) yeilds this formula... =SUMPRODUCT((MOD(ROW(E3:E301)-3,4)=0)*E3:E301) -- Rick (MVP - Excel) "Picman" wrote in message ... I seem to be missing something because I get an error. My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
Try this:
=SUMPRODUCT(--(MOD(ROW(E3:E301)-ROW(E3),4)=0),E3:E301) -- Biff Microsoft Excel MVP "Picman" wrote in message ... I seem to be missing something because I get an error. My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
That worked like a charm. Thanks very much guys, i appreciate all of the help.
"T. Valko" wrote: Try this: =SUMPRODUCT(--(MOD(ROW(E3:E301)-ROW(E3),4)=0),E3:E301) -- Biff Microsoft Excel MVP "Picman" wrote in message ... I seem to be missing something because I get an error. My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of more than 30 non-adjacent cells
On behalf of all of us, you're welcome!
-- Biff Microsoft Excel MVP "Picman" wrote in message ... That worked like a charm. Thanks very much guys, i appreciate all of the help. "T. Valko" wrote: Try this: =SUMPRODUCT(--(MOD(ROW(E3:E301)-ROW(E3),4)=0),E3:E301) -- Biff Microsoft Excel MVP "Picman" wrote in message ... I seem to be missing something because I get an error. My perameters are as follows: Range = "e3:e301", the first cell to be added is "e3" and the second is "e7", then "e11", then "e15" etc. Basically every fourth row. "Rick Rothstein" wrote: Actually, we can eliminate one of the function calls... =SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500) and here is the modified generalized formula (I've also changed the descriptions a little bit to make them more accurate)... =SUMPRODUCT((MOD(ROW(<StartCell:<EndCell)-<StartingRowNumber,<Interval)=0)*<StartCell:<En dCell) where StartCell and EndCell are in the same column. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It is always helpful to tell us what you have (in this case, the starting row for you data and the regular spaced interval) so we can give you an exact formula for it. Since you didn't tell us, here is an example which you can modify for your situation. Let's say your data is in Column C and starts on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart (that is, the rows you want to add up are C4, C7, C10, C13, etc.); then assuming your data does not extend beyond Row 500, this formula will sum up those rows in Column C... =SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500) A general format for this formula would be... =SUMPRODUCT((MOD(ROW(<StartRow:<EndRow)-Row(<StartRow),<Interval)=0)*<StartRow:<EndRow ) -- Rick (MVP - Excel) "Picman" wrote in message ... Yes they are at regularly spaced intervals. "John C" wrote: Are the cells at regularly spaced intervals? If not, do the cells have some unique identifier in the same row that they are in? If not, remember, there are 30 arguments in each sum, you could nest your sum statements: =SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Picman" wrote: I have a column with hundreds of populated cells, and I would like to add up the sum of more than 30 of these cells. The problem is that they are not adjacent to each other and the Excel SUM function seems to limit the number of arguments to 30. Is there a way around this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adjacent cells? | Excel Worksheet Functions | |||
| Yes or No for adjacent cells | Excel Worksheet Functions | |||
| By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
| How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
| selecting non adjacent cells | Excel Worksheet Functions | |||
Linear Mode
