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

i den här handledningen 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 datauppsättning som visas nedan:

i denna handledning kommer jag att namnge intervallet A2: A10 som namn. Framöver kommer vi att använda detta namngivna intervall i formlerna.

See Also: How to create Named Ranges in Excel.

i denna datauppsättning finns det en upprepning i NAMNINTERVALLET., För att få räkningen av unika namn från denna datauppsättning(A2:A10) kan vi använda en kombination av COUNTIF och SUMPRODUCT-funktioner som visas nedan:

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

hur fungerar denna formel?

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

• COUNTIF(namn,namn)
  • denna del av formeln returnerar en array. I ovanstående exempel 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, så det skulle returnera nummer 2 för Bob. På samma sätt uppstår Steve tre gånger och därmed 3 returneras för Steve.
• 1 / COUNTIF(namn,namn)
  • 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 av matrisen returnerar den här matrisen.
    till exempel var det första elementet i matrisen som returnerades ovan 2. När 1 är uppdelad med 2, returnerar den .5.,
• SUMPRODUCT(1 / COUNTIF(namn,namn))
  • SUMPRODUCT lägger helt enkelt till alla dessa nummer. Observera att om Bob inträffar två gånger i listan returnerar ovanstående matris .5 varhelst Bob namn dök upp i listan. På samma sätt, eftersom Steve visas tre gånger i listan, returnerar matrisen .3333333 när Steve namn visas. När vi lägger till numren för varje namn, skulle det alltid returnera 1. Och om vi lägger till alla nummer, 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 det returnera en # DIV / 0! fel.

hur hanterar du tomma celler?

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

och eftersom vi delar 1 med hela matrisen returnerar den en #DIV/0! fel.,

för att hantera detta divisionsfel vid tomma celler, använd nedanstående formel:

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

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

OBS: Denna formel skulle räkna tomma celler som ett unikt värde och returnera det i resultatet.,

i ovanstående exempel 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äknar det i slutresultatet:

=SUMPRODUCT((namn<>”)/COUNTIF(namn,namn&”))

i denna formel har vi istället för 1 som täljare använt namn<>”., Detta returnerar en rad TRUEs och FALSEs. 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 som täljaren är 0 (FALSE).

nu när vi har det grundläggande skelettet av formeln redo, 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 textvärden som är unika.,

här är formeln som kommer att räkna unika textvärden i Excel:

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

allt vi har gjort används formeln istext (namn) som täljare. Det returnerar sant när cellen innehåller text, och falskt om det inte gör det. det kommer inte att räkna tomma celler, men kommer att räkna celler som har en tom sträng (””).,

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

här är formeln som kommer att räkna unika numeriska värden i Excel

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

här använder vi ISNUMBER(NAMES) som täljare. Det returnerar sant när cellen innehåller numerisk datatyp, och falskt om det inte gör det. det räknas inte tomma celler.

Du kanske också gillar följande Excel-handledning:

• hur man räknar celler som innehåller textsträngar.
• hur man räknar antalet ord i Excel.,
• räkna celler baserat på bakgrundsfärg i Excel.
• använda flera kriterier i Excel COUNTIF och COUNTIFS funktion.