Räkna unika värden i Excel med hjälp av COUNTIF -funktionen

I den här självstudien lär du dig att räkna unika värden i Excel med hjälp av formler (COUNTIF- och SUMPRODUCT -funktioner).

Hur man räknar unika värden i Excel

Låt oss säga att vi har en datamängd som visas nedan:

För denna handledning kommer jag att namnge intervallet A2: A10 som NAMES. Framöver kommer vi att använda det här namngivna intervallet i formlerna.

Se även: Hur man skapar namngivna intervall i Excel.

I denna datamängd finns det en upprepning i NAMES -intervallet. För att få antalet unika namn från denna dataset (A2: A10) kan vi använda en kombination av COUNTIF- och SUMPRODUCT -funktioner enligt nedan:

= SUMPRODUCT (1/COUNTIF (NAMES, NAMES))

Hur fungerar denna formel?

Låt oss bryta ner denna formel för att få en bättre förståelse:

• RÄTTA (NAMN, NAMES)
  • Denna del av formeln returnerar en array. I exemplet ovan skulle det vara {2; 2; 3; 1; 3; 1; 2; 3; 2}. Siffrorna här anger hur många gånger ett värde uppstår i det givna cellområdet.
    Till exempel är namnet Bob, som förekommer två gånger i listan, därför skulle det returnera nummer 2 för Bob. På samma sätt förekommer Steve tre gånger och därför returneras 3 för Steve.
• 1/RÄTTA (NAMN, NAMES)
  • Denna del av formeln skulle returnera en array - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
    Eftersom vi har delat 1 med arrayen returnerar den denna array.
    Till exempel var det första elementet i matrisen som returnerades ovan 2. När 1 är dividerat med 2 returnerar det .5.
• SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
  • SUMPRODUCT lägger helt enkelt till alla dessa siffror. Observera att om Bob förekommer två gånger i listan, returnerar ovanstående array .5 varhelst Bob -namnet visas i listan. På samma sätt, eftersom Steve visas tre gånger i listan, returnerar matrisen .3333333 när Steve -namnet visas. När vi lägger till siffrorna för varje namn, skulle det alltid returnera 1. Och om vi lägger till alla siffror, skulle det returnera det totala antalet unika namn i listan.

Denna formel fungerar bra tills du inte har några tomma celler i intervallet. Men om du har några tomma celler skulle den returnera en #DIV/0! fel.

Hur hanteras tomma celler?

Låt oss först förstå varför det returnerar ett fel när det finns en tom cell i intervallet. Anta att vi har datauppsättningen enligt nedan (med cell A3 tom):

Om vi ​​använder samma formel som vi använde ovan returnerar COUNTIF -delen av formeln en matris {2; 0; 3; 1; 3; 1; 2; 3; 1}. Eftersom det inte finns någon text i cell A3 returneras dess räkning som 0.

Och eftersom vi delar 1 med hela den här matrisen returnerar den en #DIV/0! fel.

För att hantera detta delningsfel vid tomma celler, använd formeln nedan:

= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))

En ändring som vi har gjort i denna formel är kriteriedelen i funktionen RÄNTA. Vi har använt NAMES & ”” istället för NAMES. Genom att göra detta skulle formeln returnera antalet tomma celler (tidigare returnerade det 0 där det fanns en tom cell).

OBS: Denna formel räknar tomma celler som ett unikt värde och returnerar det i resultatet.

I exemplet ovan ska resultatet vara 5, men det returnerar 6 eftersom den tomma cellen räknas som ett av de unika värdena.

Här är formeln som tar hand om de tomma cellerna och inte räknas in i det slutliga resultatet:

= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))

I denna formel har vi i stället för 1 som täljare använt NAMN ””. Detta returnerar en matris med SANT och FALS. Det returnerar FALSKT när det finns en tom cell. Eftersom TRUE motsvarar 1 och FALSE motsvarar 0 i beräkningar, räknas inte tomma celler eftersom täljaren är 0 (FALSE).

Nu när vi har grundskelettet för formeln klart kan vi gå ett steg längre och räkna olika datatyper.

Hur man räknar unika värden i Excel som är text

Vi kommer att använda samma koncept som diskuterats ovan för att skapa formeln som bara räknar unika textvärden.

Här är formeln som räknar unika textvärden i Excel:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))

Allt vi har gjort är att använda formeln ISTEXT (NAMES) som täljare. Den returnerar SANT när cellen innehåller text och FALSK om den inte gör det. Det räknar inte tomma celler, utan räknar celler som har en tom sträng ("").

Hur man räknar unika värden i Excel som är numeriska

Här är formeln som räknar unika numeriska värden i Excel

= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))

Här använder vi ISNUMBER (NAMES) som täljare. Den returnerar SANT när cellen innehåller numerisk datatyp och FALSK om den inte gör det. Det räknar inte tomma celler.