 # 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

# Re: COUNTIFS formula with multiple criteria, counting unique values

``=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.

# Re: COUNTIFS formula with multiple criteria, counting unique values

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

Recipient type=FN, Region=Alberta, excluding the Funding type=Grant # Re: COUNTIFS formula with multiple criteria, counting unique values

``=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?