Function Problems

How to Count Unique Stores with Multiple Conditions in Excel

John Wilson Oct 7, 2026 869 views

Question details

The user needs a formula to count distinct stores that meet specific criteria (e.g., status is 'Pass' and offers more than one distinct product), while ignoring duplicate rows and time slots.

How to Count Unique Stores with Multiple Conditions in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating a unique conditional count across multiple columns without manually filtering duplicate records.
Observed behavior: The user requires an automated spreadsheet formula to evaluate distinct combinations of stores and products meeting particular conditions without double-counting recurring entries.

Before you start

Verify if your spreadsheet software supports Dynamic Array functions like UNIQUE and FILTER (available in newer versions like Microsoft 365, Excel 2021, and WPS Office). Older versions will require workaround formulas like SUMPRODUCT.

Solution 1Recommended

Use UNIQUE and FILTER Functions

This is the most efficient and robust method for modern spreadsheet users to extract and count unique items based on complex criteria without helper columns.

Dynamic arrays simplify complex counting tasks. By combining FILTER to isolate the rows meeting your conditions and UNIQUE to remove duplicates, you can easily wrap a ROWS function to count the final list.

Identify your data ranges

Assume Store Names are in Column A, Product Names in Column B, and Status in Column C. Your data spans from row 2 to 100.

Filter the data

Use the FILTER function to keep only the rows where the Status is 'Pass'. In a blank cell, type: =FILTER(A2:A100, C2:C100="Pass")

Extract unique values

Wrap the UNIQUE function around your FILTER formula to eliminate duplicate store entries. The formula becomes: =UNIQUE(FILTER(A2:A100, C2:C100="Pass"))

Count the results

Finally, enclose the entire formula within the ROWS function to count the distinct stores: =ROWS(UNIQUE(FILTER(A2:A100, C2:C100="Pass"))). To add additional conditions, multiply criteria inside the FILTER function.

Handling multiple criteria: To include an additional condition, such as ignoring blank products, use the multiplication symbol for an AND logic check: =ROWS(UNIQUE(FILTER(A2:A100, (C2:C100="Pass")*(B2:B100<>""))))

Solution 2

Use SUMPRODUCT and COUNTIFS (Legacy Approach)

If you are using an older version of Excel that does not support dynamic arrays, you can use SUMPRODUCT combined with COUNTIFS to count unique values with conditions.

Efficient Formula Processing

Solve Complex Data Counting Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and SORT. This allows you to quickly count distinct records with multiple conditions without complicated legacy formulas or sluggish spreadsheet performance.

1. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook containing the store data.
2. Enter the dynamic formula: Select a blank cell and type =ROWS(UNIQUE(FILTER(A:A, C:C="Pass"))), ensuring you adjust the column references to match your specific layout.
3. Press Enter to calculate: Hit Enter on your keyboard. WPS Spreadsheet will instantly calculate and display the count of unique stores meeting your precise conditions.

Full support for modern dynamic array functions (UNIQUE, FILTER).Seamless compatibility with Microsoft Excel formats (.xlsx and .xls).Built-in advanced data analysis tools including robust Pivot Tables.Lightweight application that processes large datasets quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my unique count formula return a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no records matching your criteria. You can prevent this by adding a fallback argument to the FILTER function, such as: FILTER(A2:A100, C2:C100="Pass", "None Found").

Can I count unique values using a Pivot Table instead of formulas?

Yes. When creating a Pivot Table, check the box that says 'Add this data to the Data Model'. Then, drag the Store field into the Values area, click Value Field Settings, and select 'Distinct Count'. You can then filter the Pivot Table by your 'Pass' status.

Does the UNIQUE function ignore blank cells?

No, the UNIQUE function treats a blank cell as a distinct value (returning it as a zero or blank). To exclude blanks, you must filter them out before extracting unique values by adding a condition like (A2:A100<>"") inside your FILTER formula.

Excel & Data Problems