UNIQUE COUNT with a conditional component

New Contributor

Hello there, I'm not sure if there is a way to do this, but I'm striking out on my end. This is not my entire spreadsheet, but something I created to give you an idea of what I'm looking to do. 

 

I need to be able to count the unique number of entries in a column, IF another column has a certain specification. In the below picture I would like to know, how many topics were attributed to the academic classification. OR how many sports were attributed to the athletic classification.

 

I already have a COUNTIF Value in for the number of academic classifications, =COUNTIF($A$2:$A$13, "Academic"), and athletic classifications, =COUNTIF($A$2:$A$13, "Athletic"). Now I need another level of information, with a unique value. Anyone know if that's possible? Any help would be greatly appreciated! Thanks - AD

 

adinkins_0-1593636996954.png

 

3 Replies

Hi @adinkins,

First of all, sort your table by classifications. that will be much easier.

Then , you can do a =SUM(1/COUNTIF(B2:B7,B2:B7)) and =SUM(1/COUNTIF(B8:B13,B8:B13)) for each topics.

I had to move the results so they don't get caught in the table sort.

Bennadeau_0-1593639707524.png

 

Ben

 

Thanks for that tip, Ben. Unfortunately this is a report that is due to our University Cabinet and is sorted by date for our executives. The analytics is on a different sheet entirely. If I sort them that way for the analytics, and then re-sort them, will the results still remain the same? - AD

@adinkins If your Excel version supports the new FILTER and UNIQUE functions, you might want to have a look at the attached workbook. It contains an example that resembles your description.