SOLVED

# COUNTIFS with unique values

Occasional Contributor

# 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

6 Replies
best response confirmed by reevesgetsaround (Occasional Contributor)
Solution

# Re: 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.

# Re: COUNTIFS with unique values

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;

# Re: COUNTIFS with unique values

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

# Re: COUNTIFS with unique values

Hi Quad, this was very helpful. Much trickier than I was expecting!

Thank You!

# Re: COUNTIFS with unique values

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

you are welcome