Forum Discussion
COUNTIFS formula with multiple criteria, counting unique values
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
=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.
If 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
=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.
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?
