Trying to calculate total savings (Budget – Actual) when Column B contains both numbers and text

Wade Blazengame

I have a Smartsheet with two columns:

• Column A Budget (numeric)
• Column B Actual Spend (mixed: numeric values, Blanks, or the text “NA”)

I need a single formula that:

• Subtracts Actual Spend from Budget
• Includes only the rows where Actual Spend is numeric
• Ignores any rows where Actual Spend is blank or contains “NA”
• Returns total cost savings for the sheet
• And I cannot create a helper/Savings column

My current attempts using SUMIF, COLLECT, and ISTEXT keep resulting in #UNPARSEABLE.

What is the correct formula to sum only numeric Actual Spend values and compute total savings (Budget – Actual) without creating an extra column?

EXAMPLE BELOW

Budget	Actual	Included?
100	80	YES
90	NA	No (text)
50	30	YES
75		No (blank)

(100 + 50) – (80 + 30) = 40

Paul Newcome

Try something along the lines of

=SUMIFS(Budget:Budget, Actual:Actual, ISNUMBER(@cell)) - SUMIFS(Actual:Actual, Actual:Actual, ISNUMBER(@cell))