Function Problems

How to Count Unique Values Based on Multiple Criteria in Excel

Steve K Oct 13, 2026 868 views

Question details

The user needs to count the distinct number of student IDs based on a specific college match and one of two specific service types.

How to Count Unique Values Based on Multiple Criteria in Spreadsheets

Product: Spreadsheets
Device & OS: not provided
Scenario: Analyzing a database where multiple records exist for the same individual, requiring a distinct count of items that meet specific multi-conditional filters (combining AND and OR logic).
Observed behavior: Standard functions like COUNTIFS do not remove duplicate entries from the count, so an advanced array formula is required to filter the dataset, extract unique values, and then tally them.

Before you start

Ensure your dataset is organized into uniform columns with clear headers, and verify that your spreadsheet software supports dynamic array functions like FILTER and UNIQUE.

Solution 1Recommended

Combine COUNTA, UNIQUE, and FILTER Functions

This is the most efficient method for returning a distinct count based on complex conditions. It uses the FILTER function to apply AND/OR logic, UNIQUE to remove duplicates, and COUNTA to count the remaining records.

In array formulas, you can perform multi-condition filtering by using mathematical operators. The asterisk (*) acts as an AND operator (both conditions must be true), while the plus sign (+) acts as an OR operator (either condition can be true).

Identify your data columns

Determine the ranges for your criteria. For example, assume Column H contains Student IDs, Column A contains College names, and Column D contains the Service types. M2 holds your target College name.

Write the FILTER logic

In an empty cell, begin with the FILTER function to isolate the records. Use the syntax: =FILTER(H2:H10000, (A2:A10000=M2) * ((D2:D10000="Virtual Drive-Thru") + (D2:D10000="In-Person Drive-Thru")))

Apply the UNIQUE function

Wrap the FILTER function inside the UNIQUE function to ensure each Student ID is only returned once: =UNIQUE(FILTER(...))

Count the results

Finally, wrap the entire formula in the COUNTA function to tally the unique values: =COUNTA(UNIQUE(FILTER(H2:H10000,(A2:A10000=M2)*((D2:D10000="Virtual Drive-Thru")+(D2:D10000="In-Person Drive-Thru"))))) and press Enter.

Combine COUNTA, UNIQUE, and FILTER Functions

Formula Customization: You can replace the hardcoded text strings (like "Virtual Drive-Thru") with cell references (like K2 and L2) to make your dashboard more interactive.

Solution 2

Use the LET Function to Extract and Count Unique IDs

If you want to view the actual list of matching unique IDs in addition to counting them, you can utilize the LET function combined with IF logic.

Advanced Spreadsheet Features

Analyze Complex Datasets Seamlessly with WPS Spreadsheet

WPS Office Spreadsheet fully supports dynamic array functions like FILTER, UNIQUE, and LET. It allows you to effortlessly process advanced multi-conditional logic and distinct counts without lagging or compatibility issues.

1. Install WPS Office: Download and install WPS Office Free on your computer or mobile device.
2. Open your dataset: Launch WPS Spreadsheet and open your existing data file.
3. Apply the array formula: Click the formula bar, paste your COUNTA, UNIQUE, and FILTER combination, and press Enter to instantly see your distinct counts.

100% compatible with advanced Microsoft Excel formulas and array logicFree, lightweight, and incredibly fast for processing large datasetsBuilt-in dynamic array spilling for immediate visual feedbackFamiliar user interface for seamless and rapid data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Can I use COUNTIFS to count unique values?

No, the COUNTIFS function tallies every single row that matches your specified criteria, including duplicate entries. To count unique values, you must combine the COUNTA, UNIQUE, and FILTER functions.

What do the asterisk (*) and plus (+) symbols mean in array formulas?

In spreadsheet array logic, the asterisk (*) represents an AND condition, meaning all linked criteria must be true. The plus sign (+) represents an OR condition, meaning the formula will return results if any of the linked criteria are true.

What happens if the FILTER function finds no matches?

If no data matches your criteria, the FILTER function returns a #CALC! error. To prevent this, you can utilize the optional [if_empty] argument in the FILTER function, such as =FILTER(range, criteria, "No matches"), or wrap the entire formula in an IFERROR function.

Excel & Data Problems