Forum Discussion
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 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
=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.
=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.
- Hi Quad, this was very helpful. Much trickier than I was expecting!
Thank You!
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!
