itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Count unique value with a formula in an excel report

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.

formula

 

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 is/are the formula(s) ?

=SUMPRODUCT()

=COUNTIF()

=SUM()

=FREQUENCY()

=IF()

=MATCH()

=ROW()

=COUNTA()

=UNIQUE()

=TEXTSPLIT()

=TEXTJOIN()

=FILTER()

=SUBSTITUTE()

If the cell has only 1 value like “TKT001”, put this formula (for instance cell F2):

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

1 criteria (for instance in cell G2)

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

NOTE: don’t forget to change “SLA” by your criteria

2 criterias (for instance in cell H2)

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

NOTE: don’t forget to change “open” and “SLA” by your criterias

If the cell has a lot of values like “TKT004, TKT005”, put this formula (for instance cell F9):

=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN(" ",1,SUBSTITUTE(A2:A10,", "," ")),," ")))

NOTE: as you can see in my example, the values are separated by a “, “ (comma space), change the section in “red” by yours

1 criteria (for instance in cell G9)

=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN(" ",1,FILTER(SUBSTITUTE(A2:A10,", "," "),(C2:C10="SLA"))),," ")))

2 criterias (for instance in cells H9)

=COUNTA(UNIQUE(TEXTSPLIT(TEXTJOIN(" ",1,FILTER(SUBSTITUTE(A2:A10,", "," "),(B2:B10="open")*(C2:C10="SLA"))),," ")))

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

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

formula

1 criteria (for instance in cell G3)

=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 cell 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))

Interesting Topics