|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using the sum function to display number as well as words in a single cell
Hi,
If I want the cell H10 to display the sum number of all cells from H1 to H9 in a column, I'd just need to use the simple sum function below. =SUM(H1:H9) But my problem is that I also need to display the word 'Total:' for the sum of all nine cells above, say if =SUM(H1:H9) is 56, then I want it to be displayed as Total: 56 in cell H10. What can I do? Thanks! http://office.microsoft.com/en-us/ex...005200127.aspx |
#2
|
||||
|
||||
Try ="Total: "&sum(h1:h9)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
|
#4
|
||||
|
||||
You're welcome
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Just a follow-on question.
I applied this to another column to calculate the sum. It displayed the word "Total: " as well as the number from its calculation. But it gives too many decimals. The 'reduce decimal' button on the toolbar doesn't work on numbers generated by functions? I only need the number in two decimal places. |
#6
|
||||
|
||||
Ask yourself how you would do it without the text part.
What are you trying to do ? Round the result to two decimals. Did I say ROUND ? OK then let's use the ROUND function =ROUND(sum(your_range),2) Now use this new formula the same way we used SUM alone before... I trust you now have the answer
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
||||
|
||||
I'm going to differ with Peco on this occasion, for one picky reason: Suppose your sum is 234.501, and you use ROUND(234.501,2)? The answer is then displayed as "234.5", whereas you (I suppose) want "234.50". My solution is to use TEXT(234.501,"0.00"), which turns 234.501 into "234.50".
Now, if this works for you then we can stop there, but it has one drawback: The whole cell cannot be used for further calculation, because it's a character string "Total: 234.50" rather than the value 234.5. There is a way you can have both the "Total:" and the numeric value available for calculation. Go back to the formatting dialogue for this cell, and select the Custom category. Fill in the following as the custom format string: Code:
"Total: "0.00 |
#8
|
|||
|
|||
Quote:
=ROUND(("Total: "&SUM(G2:G6)),2) |
#9
|
||||
|
||||
="Total: "&ROUND(SUM(G2:G6),2)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to count multiple values in a single cell, except zero? | iuliandonici | Excel | 1 | 04-13-2011 09:45 PM |
Sum & difference between multiple values in a single cell | iuliandonici | Excel | 4 | 04-13-2011 09:27 PM |
How can I break a single group of words into three sets? | Learner7 | Excel | 1 | 03-22-2011 02:35 PM |
How can I write the following (whole) formula in a single cell? | Learner7 | Excel | 1 | 07-19-2010 10:06 AM |
Pasting multiple photos - one photo per cell in a single oepration | mccruise | Word | 0 | 04-07-2010 04:13 AM |