Excel: Count duplicates only once, based on other criteria

Question

I need a formula that counts only unique values based on a third criteria.

So I got a excel sheet that lists different organizations, the location they were active in, and the year they were active in. Some organizations were active in multiple years.

So the formula needs to count the number of unique organizations that were active in a specific district over time. This cannot count organizations twice if they were active there more than one time.

The table looks something like this:

YEAR     ORGANIZATION     LOCATION

2015       Orga A          Germany
2015       Orga B          France  
2015       Orga A          Germany
2014       Orga C          Poland
2013       Orga D          Germany

... and I need a formula that will tell me that the number of active, unique organizations in Germany is 2.

Answer 1

Thanks for your input guys! The solution proposed by Máté unfortunately does not work, as it counts the same organization repeatedly if they were active more than once in the time period.

What worked for me was the following:

1. Create an extra column (in the list you are referring to) with the following formula:

   =COUNTIF($B$2:B*X*,B*X*)=1
   

   X = 2, in the first row, and than rises by 1 per row (e.g. B2, B3, B4) That will output whether it is the first time that the organization was active (as YES/NO).

2. In the cell where you want the result enter:

   =COUNTIFS(J2:J3004,"=TRUE", C2:C3004,"=GERMANY")
   

"J" Being the row where the formula was entered in step 1. This counts the number of unique organizations active in, in this case, Germany.