Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
Good afternoon,
I need to sum over 30 cells which are nonadjacent. However, they are evenly spaced (the first cell is D4, and then H4, and soforth, through FH4). Currently, I am simply using the SUM function and various "embedded" SUMs, but this is very long and tedious to input (not to mention, looks horribly sloppy in the Fx field). Is there a simpler way to tally this information? Thank you again for your kind support. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
Try this:
=SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4) -- Biff Microsoft Excel MVP "Stacy C" wrote in message ... Good afternoon, I need to sum over 30 cells which are nonadjacent. However, they are evenly spaced (the first cell is D4, and then H4, and soforth, through FH4). Currently, I am simply using the SUM function and various "embedded" SUMs, but this is very long and tedious to input (not to mention, looks horribly sloppy in the Fx field). Is there a simpler way to tally this information? Thank you again for your kind support. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
Hi,
Try this =SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Stacy C" wrote: Good afternoon, I need to sum over 30 cells which are nonadjacent. However, they are evenly spaced (the first cell is D4, and then H4, and soforth, through FH4). Currently, I am simply using the SUM function and various "embedded" SUMs, but this is very long and tedious to input (not to mention, looks horribly sloppy in the Fx field). Is there a simpler way to tally this information? Thank you again for your kind support. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
=SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0))
That'll return an error if any of the in-between cells contain text. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try this =SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Stacy C" wrote: Good afternoon, I need to sum over 30 cells which are nonadjacent. However, they are evenly spaced (the first cell is D4, and then H4, and soforth, through FH4). Currently, I am simply using the SUM function and various "embedded" SUMs, but this is very long and tedious to input (not to mention, looks horribly sloppy in the Fx field). Is there a simpler way to tally this information? Thank you again for your kind support. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
"T. Valko" wrote...
Try this: =SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4) .... This is the best approach when there's a pattern, but the really general approach would be to use multiple area ranges, e.g., =SUM((D2,D3,D5,D7,D11,D13,D17,D19,D23,D29,D31,D37, D41,D43,D47,D53,D59,D61,D67,D71,D73,D79,D83,D89,D9 7, D101,D103,D107,D109,D113,D127,D131,D137,D139,D149) ) Note the inner set of parentheses. That's one argument to SUM. That one argument is a 35 area range. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing +30 Non-adjacent cells
I had entered the individual columns, but this spreadsheet will always have
the fields evenly spaced. I just wanted something a bit cleaner than a bunch of parentheses :) Thank you kindly. "Harlan Grove" wrote: "T. Valko" wrote... Try this: =SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4) .... This is the best approach when there's a pattern, but the really general approach would be to use multiple area ranges, e.g., =SUM((D2,D3,D5,D7,D11,D13,D17,D19,D23,D29,D31,D37, D41,D43,D47,D53,D59,D61,D67,D71,D73,D79,D83,D89,D9 7, D101,D103,D107,D109,D113,D127,D131,D137,D139,D149) ) Note the inner set of parentheses. That's one argument to SUM. That one argument is a 35 area range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Yes or No for adjacent cells | Excel Worksheet Functions | |||
By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
summing values from adjacent column with refrence from adjacent column | Excel Discussion (Misc queries) | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
Lookup value throughout an array_return adjacent value and summing | Excel Worksheet Functions |