Function Problems

How to Use COUNTIFS for Non-Blank Cells and Time Ranges

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to count the number of deliveries that have a non-blank delivery reference and fall within specific time shifts.

Product: Spreadsheets
Device & OS: not provided
Scenario: Counting delivery records based on multiple criteria: specific time ranges and non-empty reference fields.
Observed behavior: The COUNTIFS formula returns incorrect totals because some cells appear blank visually but actually contain hidden zero values or formula-driven empty strings.

Before you start

Check if your seemingly blank cells contain actual zeros, spaces, or formulas returning empty strings, as these can disrupt standard COUNTIFS logic.

Solution 1Recommended

Use COUNTIFS with the TIME Function and Exclusion Criteria

Apply a multiple-criteria COUNTIFS formula that checks for non-blank values, optionally excludes hidden zeros, and bounds the time using the TIME function.

To properly count deliveries within a specific shift, you must combine time operators with non-blank operators. If your spreadsheet is configured to hide zero values, you must explicitly exclude zeros from your formula to prevent them from inflating your count.

Select the target cell

Click on the cell where you want the final delivery count for the shift to appear.

Enter the base COUNTIFS formula

Type =COUNTIFS(F876:F905, "<>", C876:C905, ">="&TIME(6,0,0), C876:C905, "<"&TIME(14,0,0)). This checks if column F is not blank and column C is between 06:00 and 13:59.

Exclude hidden zero values

If your worksheet displays zeros as blank cells, they will be counted. Modify the formula to add an exclusion for zero: =COUNTIFS(F876:F905, "<>", F876:F905, "<>0", C876:C905, ">="&TIME(6,0,0), C876:C905, "<"&TIME(14,0,0)).

Adjust for other shifts

Copy the formula for other shifts and change the TIME parameters, for example, TIME(14,0,0) to TIME(22,0,0) for an afternoon shift.

Handling Empty Strings: If column F contains formulas that return empty strings (""), the "<>" operator might still count them. In that case, replacing "<>" with "?*" will force the formula to only count cells containing actual text characters.

Advanced Formula Support

Master Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like COUNTIFS and TIME, making it easy to analyze complex datasets, employee schedules, and delivery logs.

1. Open your workbook: Launch WPS Spreadsheet and open your .xlsx or .xls delivery log file.
2. Insert the function: Click on the 'Formulas' tab and select 'Insert Function'.
3. Use the formula wizard: Search for 'COUNTIFS' and follow the intuitive wizard to input your criteria ranges without worrying about syntax errors.
4. Save and share: Save your file seamlessly in Microsoft Excel formats to share with colleagues and clients.

Fully compatible with Microsoft Excel formulas and formats (.xlsx)Pre-built function library for quick and accurate calculationsAdvanced data validation and conditional formatting to spot hidden zerosFree, lightweight, and incredibly easy to learn

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS count blank cells when using the "<>" criteria?

Cells containing formulas that return empty strings (""), hidden zero values, or invisible spaces are not truly empty. The COUNTIFS function evaluates them as containing data, so they meet the "<>" (not equal to blank) criteria.

How do I exclude zero values in my COUNTIFS formula?

You can add an additional condition to your formula targeting the exact same range. For example, adding `F876:F905, "<>0"` to your COUNTIFS arguments ensures that cells containing the number zero are completely excluded from the final count.

Can I use cell references instead of the TIME function in COUNTIFS?

Yes, you can type your shift start and end times in separate cells (e.g., A1 and A2) and reference them in your formula using concatenation. For example, use `C876:C905, ">="&A1` instead of manually typing the TIME function.

Excel & Data Problems