Formula Errors

Fix COUNTIFS #VALUE! Error When Excluding Columns in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to count blank cells across a specific range while excluding one column, but attempting to supply disjointed ranges to a single COUNTIFS function triggers a #VALUE! error.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Counting blank cells across adjacent columns (e.g., C through O) but needing to skip a specific column (e.g., column H) during the evaluation.
Observed behavior: The Excel COUNTIFS formula returns a #VALUE! error because it cannot process disjointed or non-contiguous ranges within a single argument.

Before you start

Ensure that the multiple ranges you plan to evaluate reside on the same worksheet and have identical dimensions to avoid referencing errors.

Solution 1Recommended

Combine Multiple COUNTIFS Functions Using Addition

Since the standard COUNTIFS function cannot process separated ranges (like C5:G5 and I5:O5) in one go, the most reliable fix is to calculate each contiguous block in its own COUNTIFS function and add the results together.

The #VALUE! error occurs because Excel's IFS family of functions strictly requires contiguous ranges. By splitting your criteria into two distinct calculations and combining them with a simple mathematical operator, Excel can successfully evaluate the data while entirely skipping your excluded column.

Select the target cell

Click on the cell where you want the final formula evaluation to appear (e.g., the cell currently displaying the #VALUE! error).

Write the first COUNTIFS segment

Enter the formula for the range before the excluded column. For example: COUNTIFS('3RD SHIFT CHECKS'!C5:G5,"")

Add the second COUNTIFS segment

Type a plus sign (+) and append the function for the range after the excluded column. For example: + COUNTIFS('3RD SHIFT CHECKS'!I5:O5,"")

Wrap in your logical IF statement

Incorporate the combined counts into your main IF logic. Example: =IF('3RD SHIFT CHECKS'!B5="NIU","NIU",IF(COUNTIFS('3RD SHIFT CHECKS'!C5:G5,"")+COUNTIFS('3RD SHIFT CHECKS'!I5:O5,"")>0,"VERIFY","PASS"))

Apply formula to other rows

Press Enter to confirm the formula, then click and drag the fill handle down to apply the fixed formula to the remaining rows in your shift worksheet.

Formula Syntax Tip: Make sure that both COUNTIFS ranges are exactly the same height (e.g., both referencing row 5) to ensure your row-by-row checks remain accurate as you drag the formula down.

Advanced Spreadsheet Editor

Fix Complex Formula Errors Seamlessly with WPS Spreadsheet

WPS Spreadsheet is a powerful, user-friendly data analysis tool that perfectly supports advanced mathematical functions, including nested IFs and COUNTIFS. Resolve disjointed range errors effortlessly in a familiar interface without losing data integrity.

1. Open your workbook: Launch WPS Office and open your existing spreadsheet containing the #VALUE! error.
2. Locate the broken formula: Click on the cell returning the error and navigate to the formula bar at the top.
3. Apply the multi-range fix: Replace the broken single range with the additive COUNTIFS method (e.g., COUNTIFS(...) + COUNTIFS(...)) to safely bypass the excluded column.
4. Calculate and deploy: Press Enter to instantly resolve the error, and use the fill handle to drag the corrected formula down your column.

100% compatible with Microsoft Excel formulas and .xlsx formatsBuilt-in error checking and formula auditing capabilitiesLightweight architecture handles large shift datasets without laggingIntuitive formula entry with auto-complete suggestions

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS return a #VALUE! error when selecting two ranges?

The COUNTIFS function natively expects criteria ranges to be contiguous and symmetrical blocks. Supplying a union of disjointed ranges (like C5:G5, I5:O5) inside a single criteria argument breaks this structural requirement, forcing Excel to return a #VALUE! error.

Can I use SUMPRODUCT instead of COUNTIFS to exclude a column?

Yes. While SUMPRODUCT can handle arrays and multiply boolean logic to exclude columns, combining multiple COUNTIFS functions with a plus sign (+) is generally much easier to read, quicker to type, and less taxing on spreadsheet performance when applied to thousands of rows.

Does this #VALUE! error workaround apply to SUMIFS and AVERAGEIFS?

Yes. The entire 'IFS' family of functions shares the exact same strict contiguous range requirements. To exclude columns in a SUMIFS calculation, you must sum multiple individual SUMIFS functions together.

How do I exclude multiple non-adjacent columns in a large dataset?

If you need to skip several non-adjacent columns (e.g., columns D, H, and M), you will need to chain additional '+ COUNTIFS(...)' segments for every valid contiguous block in between those columns, or restructure your sheet to group the relevant data together.

Excel & Data Problems