How to count unique values in a column based on 2 conditions (in different columns)

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version
How to count unique values in a column based on 2 conditions

I just want to share a sample worksheet in Excel where you might find helpful in your work. The worksheet does exactly what the title says: "counting unique values" in one column based on the conditions in other columns.

This is especially useful if you are working on raw data especially those exported from your accounting systems or inventory systems and data are just tabulated in columns which of course contains several duplicates. When you are to count such unique data, this worksheet can be helpful.   

Sample raw data for unique counting
 
In this sample worksheet, the goal is to count the IDs in all areas that are coming from leads and not from leads. That is the YES and NO in column B. The sample report looks like this:
 
Sample required report that counts unique values
 
 
The ID Count column in the Report table simply counts all IDs that match the Area column AND the Lead? column. The Unique IDs column now counts only the unique IDS, so in general, it must be less than or equal to the ID Count column.
 
Based on the sample Raw Data table, here is the completed Report table showing the correct ID Count and Unique IDs:
 
Sample report showing unique count
 

Formulas used in the Report table

There are only 2 formulas needed in the report. First, the ID Count column and the formula should be:
 
=COUNTIFS(A:A,F3,B:B,G3)

This formula is tricky since it is counting IDs but it is actually NOT taking into consideration the ID column. It is only counting the entire column A and B where the values match with the values in F and G columns. This works because ANY match always has an ID regardless if it is unique or a duplicate.

The second formula is the most tricky one and it is the focus of this demo file. It is counting the unique ID based on the matching F anf G values. The formula is an array formula and it should be:

=SUM(IF(FREQUENCY(IF($A$3:$A$25=F3,IF($B$3:$B$25=G3,IF($C$3:$C$25<>"",MATCH("~"&$C$3:$C$25,$C$3:$C$25&"",0)))),ROW($C$3:$C$25)-ROW($C$3)+1),1))

This formula is an ARRAY formula so CTRL+SHIFT+ENTER must be pressed to make it an array formula. Curly braces will then enclose the entire formula to indicate that it is an array formula.

This formula simply matches the content in F and G then counts the unique IDs in column C. You can download the demo file below.

 
 
 
file_attachments: