How to Modify COUNTIFS to Require Multiple Blank Columns in Excel
Question details
The user needs to modify an existing COUNTIFS formula to count rows where a date matches today and multiple specific columns (J, A, B, C) are strictly empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking deadlines or statuses where specific task stages or data points (represented by columns) remain incomplete or empty.
- Observed behavior
- The current formula correctly checks if column J is blank, but needs to be expanded to evaluate whether columns A, B, and C are also simultaneously blank before including the row in the count.
Verify that all the column ranges you plan to reference in your COUNTIFS formula are exactly the same size (e.g., all spanning from row 9 to 70), as mismatched ranges will immediately return a #VALUE! error.
Expand COUNTIFS to Include Additional Blank Criteria
Add multiple range-criteria pairs to the COUNTIFS formula to check for empty cells using the empty string symbol ("").
The COUNTIFS function evaluates multiple conditions simultaneously. By appending additional range arguments paired with the criteria "", you force the formula to evaluate whether those specific cells contain no data. All conditions must be true for the row to be counted.
Select the cell containing your current COUNTIFS formula. Click into the Formula Bar at the top of the sheet or press F2 to begin editing.
After your last existing criteria, type a comma to add a new criteria range. Select column A's data range (e.g., $A$9:$A$70), add another comma, and type "" (two double quotation marks) to signify a blank cell.
Repeat this pattern for columns B and C. Your expanded formula should look exactly like this: =COUNTIFS($H$9:$H$70,"="&TODAY(),$J$9:$J$70,"",$A$9:$A$70,"",$B$9:$B$70,"",$C$9:$C$70,"").
Press Enter. The cell will now display the exact count of rows that have today's date in column H, while being entirely blank in columns J, A, B, and C.
Use SUMPRODUCT for Complex Blank Logic
If you need to evaluate many columns at once or prefer a Boolean logic approach, SUMPRODUCT offers a highly flexible alternative.
Write and Edit Complex Formulas Seamlessly with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced functions like COUNTIFS and SUMPRODUCT, making it easy to manage complex data criteria, set conditional counts, and track daily deadlines without missing a beat.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to calculate blank columns.
- 2. Select the target cell: Click the cell where you wish to output the conditional row count.
- 3. Insert the COUNTIFS function: Go to the Formulas tab on the top ribbon, click 'Insert Function', and search for COUNTIFS, or simply type =COUNTIFS( directly into the cell.
- 4. Define ranges and criteria: Follow the on-screen formula hints or manually type your criteria: =COUNTIFS($H$9:$H$70, TODAY(), $J$9:$J$70, "", $A$9:$A$70, ""). Press Enter to execute.
Frequently Asked Questions
Why is my COUNTIFS formula returning a #VALUE! error when checking multiple columns?
A #VALUE! error in a COUNTIFS formula almost always occurs because the criteria ranges are not the exact same size. For instance, if you reference rows 9:70 in column H but rows 9:71 in column J, the formula cannot evaluate the arrays symmetrically and will fail.
Can I use the ISBLANK function inside a COUNTIFS formula?
No, COUNTIFS requires strict range and criteria text pairs and does not support embedding logical functions like ISBLANK directly as the criteria argument. You must use the double quotation marks ("") to denote a blank cell condition.
Does COUNTIFS count cells containing formulas that return an empty string as blank?
Yes, if a formula in a referenced cell evaluates to an empty string (e.g., =IF(A1=1, "", "Yes")), the COUNTIFS function using "" as the criteria will successfully count it as a blank cell, even though it technically contains underlying formula text.
How do I modify the COUNTIFS formula to count cells that are NOT blank?
To count cells that are not blank, replace the empty string criteria with "<>" (the less than and greater than symbols combined). For example: =COUNTIFS($A$9:$A$70, "<>").
