1

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.

  • 1
    it seems to be a simple countifs, =COUNTIFS(A:A,2015,C:C,"Germany") gives you the number of active, unique organizations in Germany (as far as no entire lines are duplicated). Or even with a pivot table you can achieve similar results – Máté Juhász Aug 12 '15 at 11:25
  • 1
    Welcome to Super User. This is not a script / formula writing service. We are here to help you with what you have already attempted to resolve this on your own. Share what formula you have and what errors/trouble you are having, then we will help you the best we can. – CharlieRB Aug 12 '15 at 11:27
  • @MátéJuhász It's not that simple. How do we ensure organizations are only counted once? – User15 Aug 12 '15 at 21:00
  • Why number of active organizations in Germany is two III your example? Both are "Org A" for 2015. – Máté Juhász Aug 13 '15 at 3:05
  • @User15 "Some organizations were active in multiple years." It's not obvious for me that start contains identical rows, that's why I'm offering an easy solution first. – Máté Juhász Aug 13 '15 at 3:08
-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.