'%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 When Counting Nonblank Cells
Question details
The user encounters a #VALUE! error when using the COUNTIFS function with an asterisk to count nonblank cells.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using the COUNTIFS formula with multiple criteria ranges, including a wildcard (*) for nonblank entries.
- Observed behavior
- The formula returns a #VALUE! error instead of the expected cell count.
Double-check the cell references in your COUNTIFS formula to identify all the criteria ranges you are currently using.
Make All Criteria Ranges the Same Size
The COUNTIFS function requires all criteria ranges to have the exact same number of rows and columns. Mismatched ranges will trigger a #VALUE! error.
A common mistake when building multi-criteria formulas is combining an entire-column reference (like A:A) with a specifically defined range (like H2:H200). Because these two ranges have a different number of rows, the spreadsheet software cannot match the criteria row by row, resulting in a #VALUE! error.
Click on the cell displaying the #VALUE! error to view the formula in the Formula Bar.
Look at the range arguments you have entered. For example, if you see =COUNTIFS('Project1'!A:A, "Jan", 'Project1'!H2:H200, "*"), the ranges A:A and H2:H200 do not match.
Modify the shorter or longer range so they span the exact same number of rows. Change A:A to A2:A200 to match the second range.
Press Enter to save the updated formula, such as =COUNTIFS('Project1'!A2:A200, "Jan", 'Project1'!H2:H200, "*"). The correct count should now appear.
Use WPS Spreadsheet for Accurate Formula Calculations
WPS Spreadsheet provides comprehensive formula support, including COUNTIFS, with built-in argument wizards to help you easily align range sizes and avoid common syntax errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Insert the function: Select a blank cell, go to the 'Formulas' tab, and click 'Insert Function'.
- 3. Use the formula wizard: Search for COUNTIFS. The function dialog box will provide separate input fields for each Criteria_Range, making it easier to ensure they are visually identical in size.
Frequently Asked Questions
Why does the asterisk (*) work for counting nonblank cells?
The asterisk is a wildcard character that represents any sequence of text characters. When used as criteria in COUNTIFS, it effectively counts any cell containing text strings, bypassing empty cells.
Can I count nonblank cells containing numbers using an asterisk?
No, the asterisk wildcard only works for text values. To count cells containing numbers, dates, or any nonblank value, it is better to use the criteria "<>" instead of "*".
Does COUNTIFS treat formulas returning empty strings ("") as blank?
If a formula returns an empty string (""), the cell is not technically blank to some functions. However, using the "*" wildcard criteria will typically skip these empty strings, making it a popular workaround for visually blank cells.
