Adding multiple cells with embedded SUM functions

[Tommykk]

Hi! I'm a bit of an excel newbie, so this is my first post here!

I've recently discovered the ability to embed SUM functions with text, which I'm using to, for example, have the text "Total Cost: " and a SUM function in the same cell. However, I'm curious (and slightly confused) as to if it is possible to then add two of these cells together.

In the example spreadsheet I've attached I have two different tables being summed to two totals in the previously mentioned way. I'm then trying to add these two cells together in the same way, which is where I encounter the #VALUE error. I've highlighted the cell I'm having trouble with in green.

Hopefully this is possible! Thank you in advance to anyone who might be able to help with this problem!

Additional information:

Operating System: MacOS Big Sur 11.1

Excel Version: 16.53

[#2 · Accepted solution]

@Tommykk 

I'd do it slight;y differently: use only the simple sum formulas in the cells, and use custom number formatting to add the text. See the attached version.

[#3]

Wow! What an eye opener!

This is great info, I'm sure to use this in the future. A follow up question though, is it possible to save a custom number formatting as a sort of preset to speed up the process of applying it to cells?

[#4]

@Tommykk 

You can create a new style with the desired custom formatting.

You can then apply the style to selected cells.