Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUM every other 14th cell
Is there a worksheet function that sums a given range, but every other 14th cell? Example: A1=4 A15=2 A29=4 Answer would obviously be: 10 I want the function to only add every other 14th cell. I assume it would be something like this: =SUM(range,step) or something like that I have a LOT of values to add (example:A1:A1500) and it would be a LOOOOONG line of code to add to type them all in manually. And if i wanted to add maybe 20 more of these cells (280 total cells) it would be MUCH easier to change the range in the function instead of manually entering in each specific cell. *There is no specific criteria either, just numbers. So any ideas would be great. Thanks! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#2
|
|||
|
|||
Try... =SUMPRODUCT(--(MOD(ROW(A1:A1500)-ROW(A1),14)=0),A1:A1500) Hope this helps! malik641 Wrote: Is there a worksheet function that sums a given range, but every other 14th cell? Example: A1=4 A15=2 A29=4 Answer would obviously be: 10 I want the function to only add every other 14th cell. I assume it would be something like this: =SUM(range,step) or something like that I have a LOT of values to add (example:A1:A1500) and it would be a LOOOOONG line of code to add to type them all in manually. And if i wanted to add maybe 20 more of these cells (280 total cells) it would be MUCH easier to change the range in the function instead of manually entering in each specific cell. *There is no specific criteria either, just numbers. So any ideas would be great. Thanks! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#3
|
|||
|
|||
Malik641, try something like this,
=SUMPRODUCT((MOD(ROW(A1:A1500)-CELL("Row",A1:A1500)+0,14)=0)*(A1:A1500)) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "malik641" wrote in message ... Is there a worksheet function that sums a given range, but every other 14th cell? Example: A1=4 A15=2 A29=4 Answer would obviously be: 10 I want the function to only add every other 14th cell. I assume it would be something like this: =SUM(range,step) or something like that I have a LOT of values to add (example:A1:A1500) and it would be a LOOOOONG line of code to add to type them all in manually. And if i wanted to add maybe 20 more of these cells (280 total cells) it would be MUCH easier to change the range in the function instead of manually entering in each specific cell. *There is no specific criteria either, just numbers. So any ideas would be great. Thanks! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#4
|
|||
|
|||
=SUMPRODUCT(A1:A500,--(MOD(ROW(A1:A500),14)=1))
"malik641" wrote: Is there a worksheet function that sums a given range, but every other 14th cell? Example: A1=4 A15=2 A29=4 Answer would obviously be: 10 I want the function to only add every other 14th cell. I assume it would be something like this: =SUM(range,step) or something like that I have a LOT of values to add (example:A1:A1500) and it would be a LOOOOONG line of code to add to type them all in manually. And if i wanted to add maybe 20 more of these cells (280 total cells) it would be MUCH easier to change the range in the function instead of manually entering in each specific cell. *There is no specific criteria either, just numbers. So any ideas would be great. Thanks! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#5
|
|||
|
|||
Thanks everyone!!! They all work great I have another question (REALLY simple)...I never understood why there are the double dashes (or two minus signs, whatever you want to call it) before certain functions (in this case, before the MOD function). Why is that???? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#6
|
|||
|
|||
Check out this links of John McGimpsey:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "malik641" wrote in message ... Thanks everyone!!! They all work great I have another question (REALLY simple)...I never understood why there are the double dashes (or two minus signs, whatever you want to call it) before certain functions (in this case, before the MOD function). Why is that???? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#7
|
|||
|
|||
Good stuff. Thanks RD! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#8
|
|||
|
|||
I'm sure John appreciates your appreciation!<g
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "malik641" wrote in message ... Good stuff. Thanks RD! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#9
|
|||
|
|||
Hi,
Please find herein another solution. Assuming your range of data is in A1:A30 (1 to 30). In cell A32, enter the following array formula (Ctrl+Shift+Enter). =SUM(IF(MOD(ROW(A1:A30),14)=0,A1:A30)) Hope this helps. Regards, Ashish Mathur "malik641" wrote: Is there a worksheet function that sums a given range, but every other 14th cell? Example: A1=4 A15=2 A29=4 Answer would obviously be: 10 I want the function to only add every other 14th cell. I assume it would be something like this: =SUM(range,step) or something like that I have a LOT of values to add (example:A1:A1500) and it would be a LOOOOONG line of code to add to type them all in manually. And if i wanted to add maybe 20 more of these cells (280 total cells) it would be MUCH easier to change the range in the function instead of manually entering in each specific cell. *There is no specific criteria either, just numbers. So any ideas would be great. Thanks! -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
#10
|
|||
|
|||
Has anyone noticed that this thread has been repeating itself???? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=387585 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |