# Count Unique values based on multiple criteria

Options

Hi, Happy New Year to you all!!

I am trying to create an excel formula which will count the number of unique customer IDs in row 2 based on the month value (eg "October") in row 12 and the status of their review (eg "Fail") in row 41.

I have been running around this for hours looking at UNIQUE and SUMPRODUCT, SUM/IF/COUNTIF and I cannot get the result I need so I thought I would ask the experts.

Nick

Options

this is what I am currently using to calculate the number of unique values: =SUMPRODUCT(1/COUNTIFS('Agent'!B2:OZ2,'Agent'!B2:OZ2&""))-1

however I need to add in the extra criteria to say only count them if the month is also 'October' and the status is 'Fail'

thanks

• ✭✭✭✭✭
Options

Have you tried using COUNTIFS

=COUNTIFS([Customer IDs]:[Customer IDs], "October", [Month]:[Month], "Fail", [Status of Review]:[Status of Review])

Hope this helps!

• Options

Hi Christian

Many thanks for coming back to me however there are multiple instances of the same customer ID and I need to know the number of unique IDs which have failed in October rather than the total number of fails accross all IDs in October.

thanks

Options

this is what I am currently using to calculate the number of unique values: =SUMPRODUCT(1/COUNTIFS('Agent'!B2:OZ2,'Agent'!B2:OZ2&""))-1

however I need to add in the extra criteria to say only count them if the month is also 'October' and the status is 'Fail'

thanks

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!