'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Count Unique Records with Multiple Criteria in Excel
Question details
The user needs to count unique combinations of data entries while applying multiple specific criteria, such as size ranges and building names, without relying on helper columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset by multiple conditions (e.g., specific building name and size range) and then counting only the unique combinations of records that meet those exact conditions.
- Observed behavior
- Standard COUNTIF or COUNTIFS functions calculate total occurrences rather than unique records, making it difficult to tally distinct combinations based on complex multiple criteria in a single cell.
Ensure you are using a modern spreadsheet version (such as Excel 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions like UNIQUE and HSTACK.
Use UNIQUE, HSTACK, INDEX, and SUMPRODUCT Functions
Combine dynamic array functions with SUMPRODUCT to extract unique records and evaluate multiple mathematical conditions simultaneously in a single formula.
By using HSTACK, you can combine non-adjacent columns into a single array. The UNIQUE function then removes any duplicate rows from this new array. Finally, INDEX allows you to isolate specific columns from the unique array so SUMPRODUCT can apply your multiple criteria and count the matching rows.
Click on the cell where you want the final count of unique records to be displayed.
Construct the base of your formula by typing UNIQUE(HSTACK(Range1, Range2, Range3)). For example: UNIQUE(HSTACK(SourceData!E2:E500, SourceData!G2:G500, SourceData!H2:H500)).
Wrap the UNIQUE formula in an INDEX function to target specific columns for your criteria checks. For example, INDEX(UNIQUE(...), , 1) will return the first column of your unique array.
Set up your logical tests by multiplying them together inside a SUMPRODUCT function. For instance: =SUMPRODUCT((INDEX(...) = "BuildingA") * (INDEX(...) >= 10) * (INDEX(...) <= 20)).
Press Enter to calculate the result. The formula will evaluate the arrays and return the exact count of unique combinations meeting your defined parameters.
Easily Handle Complex Formulas with WPS Office
WPS Spreadsheet provides robust support for advanced dynamic array formulas. You can seamlessly count unique records with multiple criteria using functions like UNIQUE, HSTACK, and SUMPRODUCT.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook containing the source data.
- 2. Select the output cell: Click the exact cell where you want your complex count calculation to appear.
- 3. Input the array formula: Type out your formula utilizing SUMPRODUCT, UNIQUE, HSTACK, and INDEX to evaluate your criteria.
- 4. Calculate instantly: Press Enter. WPS Spreadsheet will immediately process the arrays and display your precise, filtered unique counts.
Frequently Asked Questions
Why doesn't COUNTIFS work for counting unique values?
The COUNTIFS function is designed to count all instances that meet specified criteria, including duplicates. It does not have a built-in mechanism to filter out duplicate records before counting, which is why dynamic arrays like UNIQUE are necessary.
What does the HSTACK function do in this formula?
HSTACK horizontally combines separate, non-adjacent ranges or arrays into a single continuous array. This is critical for creating a unified table of data that the UNIQUE function can then process to find distinct combinations.
Can I use this formula in older versions of Excel?
No, functions like UNIQUE and HSTACK are dynamic array functions introduced in newer versions of spreadsheet software. You will need Microsoft 365, Excel 2021, or a fully updated version of WPS Office to execute them.
How does SUMPRODUCT evaluate multiple criteria?
In SUMPRODUCT, multiplying criteria arrays together (e.g., (Array1=Condition1)*(Array2=Condition2)) acts like an AND logic gate. True evaluations become 1s and False evaluations become 0s. The formula then sums the results, effectively counting how many rows met all conditions.
