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

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

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

=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

Thanks everyone!!! They all work great :cool:

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

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 :cool:

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

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

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

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

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