Formula Errors

Fix SUM Not Adding VLOOKUP Results in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

The user needs SUM to include numeric values returned by VLOOKUP.

Fix SUM Not Adding VLOOKUP Results in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses VLOOKUP to retrieve values that are then added with SUM.
Observed behavior
The returned values appear to be numbers but are stored as text, so SUM does not include them.
Before you start

Select a VLOOKUP result cell and check the formula bar to identify the lookup formula. You can test whether its result is a number with =ISNUMBER(C2), replacing C2 with the result cell; FALSE indicates it is not numeric.

Solution 1Recommended

Multiply the VLOOKUP Result by 1

Use this method to convert numeric text returned by VLOOKUP into numbers that SUM can add.

1
Edit the lookup formula

Select the cell containing VLOOKUP and click the formula bar. Add *1 after the closing parenthesis, for example: =VLOOKUP($B17,Table_2[#All],COLUMN('Table 2'!B3),0)*1.

2
Apply the formula

Press Enter, then drag the cell's fill handle down to update the other VLOOKUP formulas in the range.

3
Check the total

Select the total cell and confirm its SUM formula includes the updated result cells, such as =SUM(C2:C10).

Multiply the VLOOKUP Result by 1
Check nonnumeric results: Multiplying by 1 converts numeric text, but genuinely nonnumeric text can produce a #VALUE! error. Check the source value if that happens.
Work with lookup formulas in WPS

Convert VLOOKUP Results for SUM in WPS Spreadsheets

In WPS Spreadsheets, multiply a VLOOKUP result by 1 to convert numeric text, then use SUM to total the converted cells.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your workbook.
  2. 2. Convert the lookup result: Select a VLOOKUP result cell and add *1 to the end of its formula in the formula bar, such as =VLOOKUP(B2,$E$2:$F$100,2,FALSE)*1.
  3. 3. Fill the formula down: Press Enter, then drag the fill handle down through the other result cells.
  4. 4. Add the results: Select the total cell and enter a SUM formula for the result range, such as =SUM(C2:C10).
Convert numeric text returned by VLOOKUP for use in SUMOpen and edit Microsoft Excel XLSX and XLS filesWork with formulas in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUM ignore my VLOOKUP results?

VLOOKUP may return values stored as text in the source table. SUM ignores text in referenced cells, even when it looks like a number.

How can I tell whether a lookup result is numeric?

Enter =ISNUMBER(C2) in an empty cell, replacing C2 with the VLOOKUP result cell. TRUE means the result is a number; FALSE means it is not.

Why do I get #VALUE! after multiplying by 1?

The returned text may contain characters or formatting that cannot be converted to a number. Check the corresponding source cell and correct the value before recalculating.

Will multiplying by 1 fix a #N/A lookup error?

No. #N/A usually means VLOOKUP did not find a match. Check the lookup value, table range, and match mode in the VLOOKUP formula.