Formula Errors

Fix Excel SUMIF #VALUE! Errors in the Sum Range

Nimra MalikNimra Malik Nov 14, 2026 869 views

Question details

The user needs to understand why a SUMIF or COUNTIF formula returns #VALUE! when its range argument contains a formula or refers to imported data.

How to Fix Excel SUMIF #VALUE! Errors in the Sum Range
Product
Microsoft Excel
Device & OS
not provided
Scenario
Using SUMIF or COUNTIF with worksheet data that may have been generated by Microsoft Access.
Observed behavior
Excel returns #VALUE! when evaluating the formula or referenced data.
Before you start

Locate the formula that returns #VALUE! and identify its criteria range and, for SUMIF, its sum range. Check whether those arguments refer to worksheet cells or contain calculations entered directly in place of ranges.

Solution 1Recommended

Use Worksheet Ranges for SUMIF and COUNTIF Arguments

Use this approach when a calculation has been entered directly where SUMIF or COUNTIF expects a range.

SUMIF expects a cell range for its range and sum_range arguments; COUNTIF expects a cell range for its range argument. If you need to calculate values before summing them, put the calculation in a helper column and reference that column.

1
Inspect the formula

Select the cell showing #VALUE! and check the formula bar for calculations used in place of the SUMIF sum_range or COUNTIF range.

2
Create a helper column

In an empty column, enter the calculation for the first data row, such as =C2*D2, and fill it down alongside the source data.

3
Reference the cells

Replace the calculated range argument with the helper-cell range. For example, use =SUMIF(A2:A100,">0",B2:B100) when A contains the criteria values and B contains the calculated amounts.

Use Worksheet Ranges for SUMIF and COUNTIF Arguments
Keep ranges aligned: Use criteria and sum ranges that start on corresponding rows and cover the same number of rows to make the formula easier to verify.
Work with formulas in WPS Spreadsheets

Check and Rewrite SUMIF Formulas in WPS Spreadsheets

Open the workbook in WPS Spreadsheets, inspect the SUMIF arguments, and use a helper column when the values to sum require a calculation.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select the Excel workbook.
  2. 2. Inspect the formula: Select the cell showing #VALUE! and review its formula in the formula bar.
  3. 3. Prepare calculated values: Enter the required calculation in an empty helper column and fill it down for the data rows.
  4. 4. Update and test SUMIF: Edit the SUMIF formula to reference the criteria cells and helper-column cells, then press Enter and check the result.
Use helper columns to prepare calculated values for SUMIF.Open and edit Microsoft Excel XLS and XLSX workbooks.Review formulas and referenced data in a familiar spreadsheet interface.Use WPS Office for free.
microsoft office alternative - wps office

Frequently Asked Questions

Can I put a calculation directly in the SUMIF sum range?

SUMIF expects a worksheet range for sum_range. Put the calculation in a helper column, fill it down, and reference those cells in the SUMIF formula.

Does COUNTIF have a sum range?

No. COUNTIF uses a range and a criterion. Make sure its range argument refers to cells rather than a calculation entered in place of a range.

How can I find errors in data imported from Access?

Select the relevant cells in Excel and use Home > Find & Select > Go To Special. Check both Formulas with Errors and Constants with Errors to find formula errors and imported error values.

Why do dates from Access not work as expected in Excel formulas?

Some imported dates may be stored as text. Select the cells and apply the General format to check whether they display as date serial numbers. If they are text, use Data > Text to Columns with the matching Date format to convert them.