Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I add every few cells
How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other
cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#2
|
|||
|
|||
Try this:
=SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#3
|
|||
|
|||
Try this Susan
=SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0)) -- Regards Ron de Bruin http://www.rondebruin.nl "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#4
|
|||
|
|||
I tried this and it said that it couldn't creat because of a circular
reference. This formula I need provides me with a number that is used in a formula for cells B8, B11, etc. Any other suggestions? I'd really appreciate it!!! "RagDyer" wrote: Try this: =SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#5
|
|||
|
|||
Ron, I got a message saying that it wouldn't work because of a circular
reference. Cells like B8 and B11 have formulas that are based on the outcome of this formula I'm tring to create. Any possible suggestions?? It would be much appreciated! - Thanks! "Ron de Bruin" wrote: Try this Susan =SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0)) -- Regards Ron de Bruin http://www.rondebruin.nl "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#6
|
|||
|
|||
Then eliminate those 2 cells from the formula, and add them separately:
=SUMPRODUCT((MOD(ROW(B13:B500),3)=1)*(B13:B500))+B 7+B10 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "SusanK" wrote in message ... I tried this and it said that it couldn't creat because of a circular reference. This formula I need provides me with a number that is used in a formula for cells B8, B11, etc. Any other suggestions? I'd really appreciate it!!! "RagDyer" wrote: Try this: =SUMPRODUCT((MOD(ROW(B7:B500)-1,3)=0)*(B7:B500)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#7
|
|||
|
|||
Glad you got it working
Thanks for your private mail Susan -- Regards Ron de Bruin http://www.rondebruin.nl "SusanK" wrote in message ... Ron, I got a message saying that it wouldn't work because of a circular reference. Cells like B8 and B11 have formulas that are based on the outcome of this formula I'm tring to create. Any possible suggestions?? It would be much appreciated! - Thanks! "Ron de Bruin" wrote: Try this Susan =SUMPRODUCT((B7:B500)*(MOD(ROW(B7:B500)-ROW(B7),3)=0)) -- Regards Ron de Bruin http://www.rondebruin.nl "SusanK" wrote in message ... How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
#8
|
|||
|
|||
You could use a helper column and sumif function. Put a character in the
helper column in each row you want to include in the sum. If you put as s in each applicable row of Col A, your formula would be =sumif (a7:A500,"S",b7.b500) "SusanK" wrote: How do I add cell B7, B10, B13 (every 3 cells up to 500+)? Note: The other cells such as B6 and B9 have other forumlas and info that is not to be included). I did this manually but the formula has now gotten so long that I get an error saying "formula too long". So I figured, there must be a shorter formula that just adds every 3 (or whatever designated) cells starting with one cell and ending with another. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Deselect Cells while using ctrl | Excel Discussion (Misc queries) | |||
Counting only active cells | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |