Count Unique values based on multiple criteria

Nick Reynolds

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.

Many thanks in advance.

Nick

Nick Reynolds

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

Christian Graf

Hi @Nick Reynolds,

Have you tried using COUNTIFS

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

Hope this helps!

Nick Reynolds

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

Nick Reynolds

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