Find formula in cell with text

[bigedge02]

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?

 

[AlphaFrog]

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]

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

 

[bigedge02]

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"

 

[bigedge02]

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

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

 

[Scott Huish]

Are any of those cells returning a formula blank ""?

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

 

[bigedge02]

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]

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]

Thanks for the suggestion AlphaFrog, that works great!