Function Problems

How to Use COUNTIFS with Multiple Criteria in the Same Column in Excel

Guest Writer Oct 10, 2026 872 views

Question details

The user needs to calculate the total number of employees for a specific manager who have either an 'office' or 'hybrid' work arrangement, ideally using a single formula.

How to Use COUNTIFS for Multiple Criteria in the Same Column

Product: Excel
Device & OS: not provided
Scenario: Counting employee records based on one mandatory condition (Manager name) and one of two alternative conditions (Work arrangement is office OR hybrid).
Observed behavior: The user struggled to combine the counts into a single formula and encountered a #VALUE! error when attempting to calculate the result.

Before you start

Verify that all data ranges referenced in your formula (e.g., the manager column and the location column) contain the exact same number of rows. Mismatched range sizes are the most common cause of the #VALUE! error in COUNTIFS.

Solution 1Recommended

Use SUM and COUNTIFS with an Array Constant

The most efficient method is to combine your OR criteria inside curly brackets (an array constant) and wrap the COUNTIFS function in a SUM function to produce a single total.

By utilizing an array constant, Excel evaluates both criteria simultaneously and returns an array of two counts. The SUM function then adds these two counts together to give you your final number.

Select the target cell

Click on the cell where you want the total count of office and hybrid employees to be displayed.

Input the SUM and COUNTIFS formula

Type the formula: =SUM(COUNTIFS(manager_column,"Steve",place_column,{"office","hybrid"}))

Adjust your cell ranges

Replace 'manager_column' and 'place_column' with the actual cell ranges of your dataset (for example, A2:A50 and B2:B50), then press Enter to calculate the result.

Use SUM and COUNTIFS with an Array Constant

Pro Tip: If you are using newer versions of Excel that support dynamic arrays, you might not even need the SUM function in some contexts, but wrapping it in SUM guarantees it works perfectly as a single total.

Solution 2

Add Two Separate COUNTIFS Functions Together

If you prefer simpler logic or are using a very old version of Excel, you can calculate the count for each work arrangement separately and add them together with a plus sign.

Work Smarter with WPS Spreadsheet

Calculate Complex Data Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array constants and functions like COUNTIFS and SUM, allowing you to filter and count complex employee datasets without errors. It offers a familiar interface and flawless compatibility with all your existing Excel files.

1. Open your dataset: Launch WPS Spreadsheet and open your employee roster file.
2. Select a result cell: Click on the specific cell where you want to calculate the manager's team size.
3. Apply the array formula: Type =SUM(COUNTIFS(A2:A100, "Steve", B2:B100, {"office","hybrid"})) and press Enter to instantly see the combined total.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Built-in formula error-checking helps you quickly identify mismatched ranges causing #VALUE! errors.Free, lightweight, and handles massive datasets with zero lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return a #VALUE! error?

A #VALUE! error in a COUNTIFS formula almost always happens because the criteria ranges are not perfectly aligned. For example, if you define the manager range as A2:A50, your location range must also be exactly B2:B50. If one is B2:B51, the formula will fail.

Can I use COUNTIFS to count more than two OR conditions in the same column?

Yes. When using the array constant method, you can add as many criteria as you need inside the curly brackets. For instance, {"office","hybrid","remote"} will count all three conditions when combined with the SUM function.

Does COUNTIFS support wildcards for partial text matches?

Yes, you can use wildcards inside your criteria. Use an asterisk (*) to match a sequence of characters or a question mark (?) for a single character. For example, "off*" will successfully count cells containing "office" as well as "offline".

What is the difference between COUNTIF and COUNTIFS?

COUNTIF evaluates only a single condition in one range. COUNTIFS allows you to evaluate multiple conditions across multiple ranges at the same time, such as verifying both the manager's name AND the employee's work location.

Excel & Data Problems