COUNTIFS formula with multiple criteria, counting unique values

Copper Contributor

I am looking for help with a formula please! The formula works for the multiple criteria however it returns the total count based on the criteria, but I need it to only count unique values in the range (only count the Recipient name once (found in Table1[Recipient])

 

I tried the =SUM(--(FREQUENCY(IF formula but couldn't figure it out based on multiple criteria and the exclusion (funding type).

 

=COUNTIFS(Table1[Region],"Alberta",Table1[Recipient],"FN",Table1[Funding Type],"<>*Grant*")

 

 

 

Thank you!

3 Replies

@xoxo-23 

=SUM(N(IF(($B$2:$B$9="Alberta")*($D$2:$D$9<>"grant"),MATCH(IF(($B$2:$B$9="Alberta")*($D$2:$D$9<>"grant"),$C$2:$C$9),IF(($B$2:$B$9="Alberta")*($D$2:$D$9<>"grant"),$C$2:$C$9),0)=ROW(1:8))))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

count unique.JPGIf you work with Office365 or 2021 you can apply FILTER and UNIQUE functions which significantly simplify the solution. 

@OliverScheurich I am looking to return the unique value of Recipient based on criteria:

Recipient type=FN, Region=Alberta, excluding the Funding type=Grant

xoxo23_0-1657906856797.png

 

@xoxo-23 

=SUM(N(IF(($C$2:$C$9="Alberta")*($D$2:$D$9<>"grant")*($B$2:$B$9="FN"),MATCH(IF(($C$2:$C$9="Alberta")*($D$2:$D$9<>"grant")*($B$2:$B$9="FN"),$A$2:$A$9),IF(($C$2:$C$9="Alberta")*($D$2:$D$9<>"grant")*($B$2:$B$9="FN"),$A$2:$A$9),0)=ROW(1:8))))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

countif.JPG

However i'm unsure if you actually want to count unique values (the subject of the discussion is: ".....counting unique values") or if you want to return the unique value(s) which are "B", "A" and "C" in the example.

 

Do you happen to work with Office365 or 2021?