Forum Discussion
COUNTIFS with unique values
=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.
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
| England | 5 | 5 |
| Northern Ireland | 3 | 3 |
| Scotland | 3 | 4 |
| Wales | 4 | 5 |
- Thank you peiyezhu, will keep this in my SQL bible!
- you are welcome
