How to count unique values in Excel with two conditions

3
votes

I know there is already a question that has been answered about counting uniques with a condition (Count Unique values with a condition), but I want to know how to count uniques in a column with TWO conditions.

I have a dataset with dates of locations created as well as city. Each location has an owner and sometimes an owner can have multiple locations so I want to count unique owners by city and month (both already exist as columns).

How can I do this?

3
excel

3 Answers

7
votes

The formula I suggested in the link is this

=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

that counts different values in A2:A100 if B2:B100 =1

You can just add more IFs with more conditions, making sure you get the requisite number of parentheses in the correct locations, e.g. for the number of different owners by city and month try this version for March in Chicago

=SUM(IF(FREQUENCY(IF(City="Chicago",IF(Month="March",IF(Owner<>"",MATCH(Owner,Owner,0)))),ROW(Owner)-MIN(ROW(Owner))+1),1))

confirmed with CTRL+SHIFT+ENTER

7
votes

To add to Barry's answer: you don't have to nest the IF's as that gets messy quickly. You can simply multiply them together like this:

IF((City="Chicago)*(Month="March"),...)

It's much easier to add variables that way and keep track of parenthesis.

1
votes

If you are using Excel 2013, there is a very simple approach w/o any formulas,

Consolidate (excel feature under DATA), arrange in a way the City names are on left, and owner names next column to right, on top row put the labels of city and owner, then select the data and click both (header and left row) options, and for the operation from top of the dialogue choose count.

You should have the report you are looking for.

Note: You also might need to remove duplicates also you can do so based on two column conditions.