I can use the conditional formatting to highlight duplicate values but to count only unique values meaning excluding the duplicate, I need a formula, especially when I need to include some additional criterias.

When I use the formula ?

To know how much of unique values based or not with more criterias.

How to use the formula ?

The formula in this topic is with ";" so depending of the operating system of your PC, the formula should have "," instead of ";". For the formula to work, except for the column E “total”, press “CRTL + SHIFT + ENTER” to put it between bracket {…}..

How are the formulas ?

=SUMPRODUCT()

=COUNTIF()

=SUM()

=FREQUENCY()

=IF()

=MATCH()

=ROW()

Put this formula (for instance cell E2):

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

1 criteria (for instance in cell F2)

{=SUM(--(FREQUENCY(IF(C2:C10="SLA",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1)>0))}

2 criterias (for instance in cells G2 and H2)

{=SUM(--(FREQUENCY(IF(B2:B10="open",IF(C2:C10="SLA",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1)>0))}

If there are blank cells, put this one (for instance cell E3):

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10&""))

1 criteria (for instance in cell F3)

{=SUM(--(FREQUENCY(IF(A2:A10<>"",IF(C2:C10="SLA",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1)>0))}

2 criterias (for instance in cells G3 and H3)

{=SUM(--(FREQUENCY(IF(A2:A10<>"",IF(B2:B10="open",IF(C2:C10="SLA",MATCH(A2:A10,A2:A10,0)))),ROW(A2:A10)-ROW(A2)+1)>0))}