Fix Excel SUMIF #VALUE! Errors in the Sum Range
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.

- 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.
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.
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.
Select the cell showing #VALUE! and check the formula bar for calculations used in place of the SUMIF sum_range or COUNTIF range.
In an empty column, enter the calculation for the first data row, such as =C2*D2, and fill it down alongside the source data.
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.

Check Referenced Data for Errors, Text Dates, and Row Limits
Use these checks when the formula already references cells but still returns an error, particularly after importing data from Access.
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. Open the workbook: Open WPS Spreadsheets and use Open to select the Excel workbook.
- 2. Inspect the formula: Select the cell showing #VALUE! and review its formula in the formula bar.
- 3. Prepare calculated values: Enter the required calculation in an empty helper column and fill it down for the data rows.
- 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.

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.




