Use Excel COUNTIFS to Count Nonblank Text and Date Cells
Question details
The user needs a COUNTIFS formula that counts both nonblank text cells and date cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains a mix of text entries and dates, and the user wants to include both types in a conditional count.
- Observed behavior
- The wildcard criterion "*" counts text entries but does not count dates because Excel stores valid dates as numeric values.
Confirm that the date cells contain real Excel dates rather than text that only looks like a date. Replace the sample range in the formulas with the range used by your existing COUNTIFS conditions.
Add separate COUNTIFS results for text and date values
Use this method when one range can contain both text and valid dates or other positive numeric values.
The "*" criterion matches text, while ">0" matches dates because Excel stores dates as serial numbers. Add the two COUNTIFS results to include both value types.
Click the cell where you want the total count to appear, then select the Formula Bar.
Enter a formula such as =COUNTIFS(A2:A100,"*")+COUNTIFS(A2:A100,">0"), replacing A2:A100 with your target range.
If your formula has another condition, repeat it in each COUNTIFS. For example: =COUNTIFS(B2:B100,"Open",C2:C100,"*")+COUNTIFS(B2:B100,"Open",C2:C100,">0").
Press Enter, then check that text entries and valid date cells are included while blank cells are excluded.
Use a numeric criterion when the range contains only dates
Use this simpler formula when the cells being counted contain dates only and no text entries need to be included.
Count Text and Date Cells with COUNTIFS in WPS Spreadsheet
WPS Spreadsheet supports familiar COUNTIFS formulas, so you can combine a text wildcard count with a numeric date count in the same way. Open your workbook, enter the formula in a result cell, and calculate text and valid date entries without changing the worksheet structure.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the data.
- 2. Select the formula result cell: Click the cell where the count should appear, then click the formula input bar.
- 3. Enter the combined COUNTIFS formula: Type =COUNTIFS(A2:A100,"*")+COUNTIFS(A2:A100,">0"), changing A2:A100 to your data range.
- 4. Apply additional criteria if needed: Add the same extra criteria to both COUNTIFS functions when counting only matching rows, then press Enter.
Frequently Asked Questions
Why does COUNTIFS with "*" not count my date cells?
The "*" criterion matches text values. Excel stores valid dates as serial numbers, so use a numeric criterion such as ">0" to count date cells.
How can I count text and dates in the same Excel range?
Add two formulas together: =COUNTIFS(A2:A100,"*")+COUNTIFS(A2:A100,">0"). The first count handles text and the second handles dates and other positive numbers.
Can I use this method with other COUNTIFS conditions?
Yes. Include your other criteria in both COUNTIFS functions. For example, count rows marked Open with =COUNTIFS(B2:B100,"Open",C2:C100,"*")+COUNTIFS(B2:B100,"Open",C2:C100,">0").
Will the ">0" criterion count blank cells?
No. Blank cells are not greater than zero, so they are excluded from the count.
Why are some date-looking cells still not counted?
They may be stored as text rather than real dates. Select a cell and check the Formula Bar or change its format through Home > Number Format; convert text dates to valid date values before using the numeric criterion.
