Function Problems

How to Count Unique Products Above 70% with Multiple Criteria in Excel

Muhammad Talha Sep 27, 2026 872 views

Question details

The user needs to find a formula to count distinct product names in column F only if the corresponding percentage in column I is over 70%, while grouping the results by department and collection in columns A through C.

How to Count Unique Products Above 70% Grouped by Department in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing conditional data analysis requiring a unique count of items that meet multiple specific numerical and text-based category criteria.
Observed behavior: The user is looking for the exact functional formula combination to accomplish this, as standard COUNTIF functions do not natively support distinct counts.

Before you start

Ensure your data ranges are identical in size (e.g., A2:A100, F2:F100) and that column I contains actual numerical percentage values (where 70% is recognized as 0.7) rather than just text.

Solution 1Recommended

Use UNIQUE, FILTER, and COUNTA Functions (Modern Excel / WPS)

This is the most efficient and robust method for modern spreadsheet applications. By combining the UNIQUE and FILTER dynamic array functions, you can easily extract and count items matching multiple criteria.

Dynamic arrays simplify complex formulas. The FILTER function will first isolate only the rows where the percentage is greater than 70% and the department matches. Then, UNIQUE removes duplicate products from that filtered list, and COUNTA counts the remaining items.

Select the target cell

Click on the empty cell where you want the final unique count result to be displayed.

Construct the FILTER function

Start by typing the FILTER function to apply your conditions. For example: =FILTER(F2:F100, (I2:I100>0.7) * (A2:A100="Dept1") * (B2:B100="Coll1"))

Wrap with UNIQUE

Enclose the FILTER formula within the UNIQUE function to ensure you only get distinct product names: =UNIQUE(FILTER(F2:F100, (I2:I100>0.7) * (A2:A100="Dept1") * (B2:B100="Coll1")))

Calculate the final count

Wrap the entire formula inside the COUNTA function to count the distinct items returned. The final formula looks like: =COUNTA(UNIQUE(FILTER(F2:F100, (I2:I100>0.7) * (A2:A100="Dept1") * (B2:B100="Coll1"), "")))

Execute the formula

Press Enter. The cell will now display the total count of unique products that meet all your specified percentage and category conditions.

Use UNIQUE, FILTER, and COUNTA Functions (Modern Excel / WPS)

Handling Empty Results: If no products meet the criteria, the formula may return a 1 (because it counts the empty string). To fix this, you can wrap the formula in an IF statement: =IFERROR(ROWS(UNIQUE(FILTER(...))), 0).

Powerful Data Analysis

Count Unique Values Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and FILTER, allowing you to perform complex conditional data analysis seamlessly without upgrading to expensive software.

1. Open your data file: Launch WPS Spreadsheet and open your .xlsx workbook containing the product data.
2. Enter the formula: Click the cell for your result and input =COUNTA(UNIQUE(FILTER(F:F, (I:I>0.7)*(A:A="YourDept")))) adjusting ranges as needed.
3. View instant results: Press Enter. The advanced calculation engine in WPS Spreadsheet will instantly compute your grouped unique counts.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports modern dynamic array functions out-of-the-box including UNIQUE, FILTER, and XLOOKUP.Lightweight, fast, and completely free to download.Familiar user interface ensuring zero learning curve for existing Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my UNIQUE and FILTER formula return a #CALC! error?

This usually happens when the FILTER function finds no data that matches your criteria (e.g., no products have a percentage above 70%). You can fix this by adding a default empty string in the FILTER function, like FILTER(range, criteria, "").

How can I count unique values with multiple criteria in older versions of Excel?

In older versions without dynamic arrays, you must use a complex array formula combining SUMPRODUCT, COUNTIFS, or FREQUENCY. For example, using SUMPRODUCT combined with COUNTIFS allows you to divide by the count of occurrences. However, upgrading to modern software like WPS Spreadsheet which supports UNIQUE/FILTER is highly recommended for simplicity.

Can I use Pivot Tables to count unique products instead of using complex formulas?

Yes. You can insert a Pivot Table and check 'Add this data to the Data Model' during creation. Place your department and collection categories in Rows, and the Product in Values. Then, change the Value Field Settings to 'Distinct Count'. You can then apply a value filter to only show items where the percentage is greater than 70%.

Excel & Data Problems