COUNTIFS with unique values

Occasional Contributor

Hello all


Would appreciate some assistance with this one please. I've been messing around with variations of SUMPRODUCTs and such on this one but just can't nail it down.


All I need is for the current COUNTIFs in columns J and K to instead return a unique count, while still ignoring blank cells and ideally without the aid of a helper column.


I'm sure its quite straightforward really.


I have attached a file with some sample data.


Many Thanks


6 Replies
best response confirmed by reevesgetsaround (Occasional Contributor)



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. If you work with Office 365 or Excel 2021 you can use the FILTER and UNIQUE functions which significantly simplify the task.

count unique values.JPG 

if with sql:


select Country,count(distinct(`Project Manager current`)) `Number of PMs currently assigned`,group_concat(distinct(`Project Manager current`)) `Names of PMs currently assigned` from basic_count_distinct_name where `Project Manager current`!='' group by Country;



select Country,(select count(distinct(`Project Manager current`)) from basic_count_distinct_name a where `Project Manager current`!='' and a.Country=b.Country) `Number of PMs currently assigned` ,(select count(distinct(`Project Manager original`)) from basic_count_distinct_name c where `Project Manager original`!='' and c.Country=b.Country) `Number of PMs assigned
` from basic_count_distinct_name b group by Country;


  • Country Number of PMs currently assigned Number of PMs assigned
Northern Ireland33
Hi Quad, this was very helpful. Much trickier than I was expecting!

Thank You!
Thank you peiyezhu, will keep this in my SQL bible!
you are welcome