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))}
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...