COUNTIFS with unique values

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 th...

sample doc 7.xlsx10 KB

excel

Formulas and Functions

OliverScheurich
Apr 24, 2023
reevesgetsaround
=SUM(N(IF(($C$3:$C$22<>"")*($E$3:$E$22=I8),MATCH(IF(($C$3:$C$22<>"")*($E$3:$E$22=I8),$C$3:$C$22),IF(($C$3:$C$22<>"")*($E$3:$E$22=I8),$C$3:$C$22),0)=ROW($1:$20))))
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.
sample doc 7.xlsx10 KB

peiyezhu

Bronze Contributor

Apr 25, 2023

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;