Solved Excel 2007: Multiple Formulas In One Cell

Hewlett-packard / Deskjet f4288
July 16, 2014 at 06:35:18
Specs: Windows 7, 4GB
I want to apply multiple formulas to single cell i.e in first case it is =SUM(D6+G6+J6) and then I want to have sum of D6 to D370.How should I do it? Thanks

gargca


See More: Excel 2007: Multiple Formulas In One Cell

Report •

✔ Best Answer
July 16, 2014 at 12:03:23
Put this in D371 and drag it over (or copy it) into the other cells:

=SUM(D6:D370)

A reference to a non-contiguous range is written as:

(D6,F7,G2,H3)

That syntax references just those 4 cells.

A reference to a contiguous range is written as:

(D6:F12)

That syntax references every cell from D6 though F12

You can also reference non-contiguous and contiguous ranges as follows:

(D6,F7,G3:H7,J6,R1:R20)

That syntax references the 3 individual cells as well as G3 though H7 and R1 through R20

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 16, 2014 at 07:30:26
First a posting tip...

When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Problem In MS Excel 2007" or "I need Excel Help", we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 16, 2014 at 07:58:01
I'm confused...

What do you mean by "sum of D6"? D6 is a single cell...there is nothing to sum.

If you want D370 to always show the same value as D6, put this in D370:

=D6

If you want to put =SUM(D6+G6+J6) in D6, you are out of luck. That will produce a Circular Reference error.

Imagine what would happen each time the worksheet calculated. Excel would add the values to D6 and then try to add those values to the new value in D6 and then try to add those values to the new value in D6 and repeat this over and over and over again non-stop.

You have 2 choices:

1 - Use any cell other than D6 for =SUM(D6+G6+J6)
2 - Use VBA (a macro) which is not restricted by "circular references" because it can evaluate the formula itself and then just put the result in the cell:

Sub SumMyCells()
  Range("D6") = WorksheetFunction.Sum(Range("D6,G6,J6"))
End Sub

In other words, VBA will perfrom the SUM operation within VBA and then write the value into the cell. If you look in D6, all you will see is the result, not a formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
July 16, 2014 at 08:36:39
Yes I agree that my statement was incomplete. Let me elaborate. I have added different cells in row 6 i.e =SUM(D6+G6+J6+M6+P6) and the result has be shown in Q6 where this formula has been applied. As I have made table upto row 370 this formula is applied to whole of the table.
Now I want to apply another formula in the same columns i.e D6 to D370, G6 to G370, J6 to J370 etc and give the sum of all the columns in row 371 respectively. Am I clear to you now?
Thanks for the help !!!

gargca


Report •

Related Solutions

#4
July 16, 2014 at 10:32:35
Sorry, but I am even more confused now than I was before.

You said Q6 contains =SUM(D6+G6+J6+M6+P6). That makes sense. (However, your formula is redundant. You are using the SUM function at the same time that you are using the addition operator +. Luckily, Excel knows what you mean. You could use either =SUM(D6,G6,J6,M6,P6) or =D6+G6+J6+M6+P6. There is no need to use both.

Then you said: As I have made table upto row 370 this formula is applied to whole of the table.

Does that mean this?

Q7 contains =SUM(D7+G7+J7+M7+P7)
Q8 contains =SUM(D8+G8+J8+M8+P8)
...
Q370 contains =SUM(D370+G370+J370+M370+P370)


Finally you said: Now I want to apply another formula in the same columns i.e D6 to D370, G6 to G370, J6 to J370 etc and give the sum of all the columns in row 371 respectively

I have no idea what you mean by that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
July 16, 2014 at 10:50:58
Does that mean this?
Q7 contains =SUM(D7+G7+J7+M7+P7)
Q8 contains =SUM(D8+G8+J8+M8+P8)
...
Q370 contains =SUM(D370+G370+J370+M370+P370)
Yes, that is correct what I mean.
Secondly, sum up D6 to D370 and write the answer in D371, sum G6 to G370 and answer in G371, sum J6 to J370 and answer in J371. Any comments !!!
Thanks

gargca


Report •

#6
July 16, 2014 at 12:03:23
✔ Best Answer
Put this in D371 and drag it over (or copy it) into the other cells:

=SUM(D6:D370)

A reference to a non-contiguous range is written as:

(D6,F7,G2,H3)

That syntax references just those 4 cells.

A reference to a contiguous range is written as:

(D6:F12)

That syntax references every cell from D6 though F12

You can also reference non-contiguous and contiguous ranges as follows:

(D6,F7,G3:H7,J6,R1:R20)

That syntax references the 3 individual cells as well as G3 though H7 and R1 through R20

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question