# Find formula in cell with text

#### bigedge02

##### New Member
Hi,

I have a basic formula in a cell that also contains text. The cell contains a rounded subtotal of a range of cells and I placed an "= " text to display in front of the output number.

In cell F111 I have:
="= "&ROUND(SUM(F4:F110), 0)

I have several of these subtotal cells and now I'm trying to get a single overall total of these subtotal cells. For example: =F111+G111+H111. I end up getting the #VALUE! error in this overall total cell.

How can I get the total of F111+G111+H111?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### AlphaFrog

##### MrExcel MVP
Why do you have the equal sign as text? If it's not needed, then remove it form the formulas. The subtotals should work then.
=ROUND(SUM(F4:F110), 0)

Or replace the equal sign using a custom cell format
"= "0

#### Scott Huish

##### MrExcel MVP
Does the #VALUE! error exist anywhere in F111:H111?

#### bigedge02

##### New Member
Why do you have the equal sign as text? If it's not needed, then remove it form the formulas. The subtotals should work then.
=ROUND(SUM(F4:F110), 0)

Or replace the equal sign using a custom cell format
"= "0

Thanks for the reply AlphaFrog. I placed the equal sign in there as text so that the output, in cell F111, reads "= 1000" rather than just "1000"

Last edited:

#### bigedge02

##### New Member
Does the #VALUE! error exist anywhere in F111:H111?

No, the error only shows up in the overall total cell.

#### Scott Huish

##### MrExcel MVP
Are any of those cells returning a formula blank ""?

You might want to use =SUM(F111:H111) instead of =F111+G111+H111

#### bigedge02

##### New Member
Are any of those cells returning a formula blank ""?

You might want to use =SUM(F111:H111) instead of =F111+G111+H111

Thanks Scott, but unfortunately I still get the #VALUE! error.

#### AlphaFrog

##### MrExcel MVP
Thanks for the reply AlphaFrog. I placed the equal sign in there as text so that the output, in cell F111, reads "= 1000" rather than just "1000"

You cannot SUM text. Adding the "= " in the formula makes the formula result as text and not numeric. You could remove the equal sign and use a custom cell format as I suggested. That would keep the cell result as numeric and also "display" the equal sign.

Another suggestion is to subtotal the source cells.
=SUM(ROUND(SUM(F4:F110), 0),ROUND(SUM(G4:G110), 0),ROUND(SUM(H4:H110), 0))

#### bigedge02

##### New Member
Thanks for the suggestion AlphaFrog, that works great!

Replies
5
Views
281
Replies
13
Views
609
Replies
1
Views
386
Replies
4
Views
166
Replies
2
Views
179

1,190,631
Messages
5,982,031
Members
439,750
Latest member
megaman777

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back