Correcting a #Value! error when words are sometimes used instead of numbers

Anonymous
2025-03-26T20:15:50+00:00

I am converting a Lotus123 sheet to Excel. In my sheet, I have one cell where I am adding 2 other cells and then subtracting a third.

Example: D2+E2-I2

My problem is that D2 sometimes contains T & M instead of numbers. I need to know how to write the formula so I do not have a #VALUE! error. This is a long sheet (three hundred lines), so I usually just copy the formula down as I go. I have tried several different formulas to try and correct the error, but I am not able to fix the problem. Thank you.

1,077.00 19667 12/11/2024 F 1,077.00
T & M #VALUE!
Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2025-03-26T20:27:03+00:00

You can use

=SUM(D2:E2)-I2

The SUM function simply ignores text values.

Or, if you do not want a result if D2 does not contain a number:

=IFERROR(D2+E2-I2, "")

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-03-26T20:48:35+00:00

    Oh, my goodness, I feel so silly now :)

    I've been using Lotus for the last several years, I would benefit from an Excel refresher.

    Thank you!

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more