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

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

Thanks Peco, it works!!

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

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.

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

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:Excel fills in the format just as though you'd typed in a standard number format but preceded by the word "Total: ", and still lets you use that cell for arithmetic calculation. If you don't need to do arithmetic on that cell then you may as well use whichever method is easiest; but this method can be pretty handy on occasion.

I didn't used SUM alone. I tried this but didn't work.

=ROUND(("Total: "&SUM(G2:G6)),2)

="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