SUM every other 14th cell

M

malik641

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!
 
P

Paul B

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
 
M

malik641

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????
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM - getting #VALUE! when some cells are blank 5
Pull and store a value on specific date in another cell 3
How do I put a variable column number in the sum() function? 7
Sum every 10th cell in a range of cells? 9
Conditional Sum in Range 2
Conditional Sum across WORKSHEET 6
Summed Cell won't update as I add data into the cell range... 2
Dynamic SUM range 8

Top