'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix COUNTIFS #VALUE! Error in Excel
Question details
The user is encountering a #VALUE! error when attempting to use the COUNTIFS function with multiple criteria ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a COUNTIFS formula to count cells based on multiple criteria, but one or more of the selected ranges differ in size.
- Observed behavior
- The formula fails to calculate the count and instead returns a #VALUE! error due to mismatched dimensions in the criteria ranges.
Click on the cell containing the error and review your formula carefully to note the exact starting and ending row and column numbers for each range you have selected.
Ensure All Criteria Ranges Have Identical Dimensions
The COUNTIFS function strictly requires every criteria range to have the exact same number of rows and columns. Mismatched shapes are the most common cause of the #VALUE! error.
If you use a multi-column range for your first criteria (e.g., J7:BR81) and a single-column range for your second criteria (e.g., CB7:CB81), Excel cannot align the data properly to evaluate it.
Click on the cell displaying the #VALUE! error to make the formula visible in the Formula Bar at the top of the screen.
Check the row and column references for each criteria range. For example, if your first range spans from row 7 to 81 (75 rows) in a single column, every other range must also span exactly 75 rows in a single column.
Modify the incorrect references so they all match in dimension. Change any multi-column or incorrectly sized ranges to perfectly mirror the shape of your primary criteria range.
Press Enter to apply the updated formula. The #VALUE! error should disappear and display the correct calculated count.
Restructure the Calculation for Multi-Column Data
If you genuinely need to evaluate a multi-column range against a single-column range, you cannot use a single COUNTIFS function and must combine multiple formulas.
Easily Calculate and Troubleshoot Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports COUNTIFS and provides intuitive, color-coded error-checking tools to help you quickly spot mismatched ranges and fix formula errors effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the #VALUE! error.
- 2. Examine the ranges: Double-click the cell with the error. WPS Spreadsheet will automatically highlight all referenced ranges in different colors.
- 3. Resize visually: Hover over the corners of the highlighted range boxes and drag them so that all boxes cover the exact same number of rows and columns.
- 4. Confirm the formula: Press Enter. WPS Spreadsheet will instantly recalculate the formula and provide the correct result.
Frequently Asked Questions
Can COUNTIFS criteria ranges be different sizes if the criteria themselves are different?
No. Regardless of what criteria you are evaluating, every single criteria range within a COUNTIFS function must have the exact same number of rows and columns. If they do not match perfectly, the function will always return a #VALUE! error.
Does COUNTIFS work with ranges located on different worksheets?
Yes, COUNTIFS can reference criteria ranges on different worksheets or even entirely different workbooks. However, the core rule remains the same: the dimensions (number of rows and columns) of those referenced ranges must be identical.
What if I get a #VALUE! error but my ranges are the exact same size?
If your dimensions match perfectly, the error might be caused by referencing a closed external workbook. The COUNTIFS function requires any linked external workbooks to be open to calculate properly; if they are closed, a #VALUE! error will occur. It can also happen if your criteria string exceeds 255 characters.
