Formula for Sum of incremental cells

  • Thread starter Thread starter amitubale
  • Start date Start date
A

amitubale

Can I have a formula for the folowing
=+C5+C17+C29+C41+C53+C65+C77+C89

(the sum is for increment of 12 cells.)

I have to uses this in 52 cells and Every time I insert new rows I hav
to modify the formula in 52 cells
 
How do you do your row inserts? Because I just made a new sheet (i
Excel 97) and inserted a row between 11 and 12 (a new row 12) With thi
in Cell A1.

=C1+C3+C5+C9+C12+C18

When I inserted the new row (right click row 12, INSERT) the formul
stayed the same result, and automatically changed to.

=C1+C3+C5+C9+C13+C19 (Because C12 and C18 shifted down 1 row)

Or, am I missing something here...

-Bo
 
I was very brief earlier. Let me explain in detail
Suppose I have the following rows.
Name_ABC
1
2
3
4

Name_DEF
7
8
9
10


Total C14
where the formula for C14=+C3+C9

I have values in 52 cells , therefore I have to calculate the total for
all the cells.
Now if I try to insert a new Set of rows for example
Name_XYZ
11
12
13
14

with additional 5 rows then my row of Total moves down by +5
So Now I have to also modify l my the formula in 52 cells
eg. C14=+C3+C9+C13
D14=+D3+C9+D13
 
I'm sorry to say that your explanation confused me more then your original
question.

Since I don't really understand what you're exactly looking for, here is a
formula that will sum every 12th row, from C5 toC89:

=SUMPRODUCT((MOD(ROW(C5:C89)-5,12)=0)*(C5:C89))

If it doesn't do what you're looking for, try again to post back with
another explanation.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I was very brief earlier. Let me explain in detail
Suppose I have the following rows.
Name_ABC
1
2
3
4

Name_DEF
7
8
9
10


Total C14
where the formula for C14=+C3+C9

I have values in 52 cells , therefore I have to calculate the total for
all the cells.
Now if I try to insert a new Set of rows for example
Name_XYZ
11
12
13
14

with additional 5 rows then my row of Total moves down by +5
So Now I have to also modify l my the formula in 52 cells
eg. C14=+C3+C9+C13
D14=+D3+C9+D13
 
Assume first data cell in a3 and you have subtotal of each "Name".

First of all, define the range a3..a100 as "zz" (no quota)
Insert | Name | Define

in cell a1, type =sum(zz)/
 
What he is trying to do is simply
sum every Nth cell

but because of the headers he inserts he needs a formula that will
work even if there is text in the range
 
I try to find simple answers to complex problems so this
is a simple answer (but it will work)

In C197 enter this formula =SUMIF($B1:$B196,"New Clients Seen:",C1:C196
In C198 enter this formula =SUMIF($B1:$B196,"Current Clients Seen:",C1:C196

Copy these formulas across all columns that you have

Keep in mind that whenever you insert new rows, the row-header
in column B must be exactly the same to get correct results. Use cop
and paste for your row-headers which seems to be what you are doing

Happy New Year from Ohio
 
There's no reason not to use Sumif for C196 also.

=SUMIF($B5:$B185,"Total Clients Scheduled:",C5:C185)

They say a picture is worth a 1000 words.

No way could this have been resolved from the descriptions given in the
post.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I try to find simple answers to complex problems so this
is a simple answer (but it will work).

In C197 enter this formula =SUMIF($B1:$B196,"New Clients Seen:",C1:C196)
In C198 enter this formula =SUMIF($B1:$B196,"Current Clients
Seen:",C1:C196)

Copy these formulas across all columns that you have.

Keep in mind that whenever you insert new rows, the row-headers
in column B must be exactly the same to get correct results. Use copy
and paste for your row-headers which seems to be what you are doing.

Happy New Year from Ohio!
 
Thanks for the replier guys. To further solve few of the problems..
How do i make a formula for C199 ?

I want to add cell in coloum "C" where cell in B ="New Client Total
This Week:" and B="Current Client Totals This Week:"

I am automating the formula because I will be adding new ROWs above al
the Total, e.g. ABC 17, ABC 18,....
That way when I insert new rows the formula will yield the correc
result.

(please refer to the test.xls Excel sheet in the previous replie
above
 
You don't have to change anything.
Your Sum formula will automatically take row additions into consideration.
Try it !
Just insert a row and see what happens to the formula.

You could however, eliminate that first plus sign, its really redundant.
=C197+C198 is sufficient (for all of your formulas).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks for the replier guys. To further solve few of the problems..
How do i make a formula for C199 ?

I want to add cell in coloum "C" where cell in B ="New Client Totals
This Week:" and B="Current Client Totals This Week:"

I am automating the formula because I will be adding new ROWs above all
the Total, e.g. ABC 17, ABC 18,....
That way when I insert new rows the formula will yield the correct
result.

(please refer to the test.xls Excel sheet in the previous replies
above)
 

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

Conditional MAX 4
Return duplicate max values in same cell 2
Increment value in a Formula 7
COUNTIF Cells in Range? 4
Duplicate max values in one cell required 2
Formulas won't increment when dragging to adjacent cells? 3
Excel formula, sum the value cell and if empty cell left blank 0
Formula help 3

Back
Top