Generisk formel
SUMPRODUCT(--(A:A=A1))
Sammanfattning
Förord
Det här är en irriterande lång introduktion, men sammanhanget är viktigt, förlåt!
Om du försöker räkna mycket långa siffror (16+ siffror) i ett intervall med COUNTIF kan du se felaktiga resultat på grund av ett fel i hur vissa funktioner hanterar långa siffror, även om dessa siffror lagras som text. Tänk på skärmen nedan. Alla räkningar i kolumn D är felaktiga - även om varje nummer i kolumn B är unikt, antyder antalet som returneras av COUNTIF att dessa siffror är dubbletter.
=COUNTIF(data,B5)
Detta problem är relaterat till hur Excel hanterar nummer. Excel kan bara hantera 15 signifikanta siffror, och om du anger ett nummer med mer än 15 siffror i Excel ser du de efterföljande siffrorna tyst omvandlas till noll. Räkneproblemet som nämns ovan härrör från denna gräns.
Normalt kan du undvika denna gräns genom att ange långa siffror som text, antingen genom att starta numret med en enda offert ('999999999999999999) eller genom att formatera cellen / cellerna som text innan du anger. Så länge du inte behöver utföra matematiska operationer på ett nummer är detta en bra lösning och det låter dig ange extra långa nummer för saker som kreditkortsnummer och serienummer utan att förlora några siffror.
Om du försöker använda COUNTIF för att räkna ett nummer med mer än 15 siffror (även när det är lagrat som text) kan du dock se opålitliga resultat. Detta händer eftersom COUNTIF internt konverterar det långa värdet tillbaka till ett nummer någon gång under behandlingen, vilket utlöser den 15-siffriga gränsen som beskrivs ovan. Utan alla siffror närvarande kan vissa nummer räknas som dubbletter när de räknas med COUNTIF.
Lösning
En lösning är att ersätta COUNTIF-formeln med en formel som använder SUM eller SUMPRODUCT. I exemplet som visas ser formeln i E5 så här ut:
=SUMPRODUCT(--(data=B5))
Formeln använder det angivna intervallet "data" (B5: B9) och genererar rätt antal för varje nummer med SUMPRODUCT.
Förklaring
Först jämför uttrycket i SUMPRODUCT alla värden i det angivna intervallet "data" med värdet från kolumn B i den aktuella raden. Detta resulterar i en rad sanna / falska resultat.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Därefter tvingar den dubbla negativa de sanna / falska värdena till 1/0 värden.
=SUMPRODUCT((1;0;0;0;0))
Slutligen summerar SUMPRODUCT bara objekten i matrisen och returnerar resultatet.
Array formel variant
Du kan också använda SUM-funktionen istället för SUMPRODUCT, men det här är en matrisformel och måste anges med kontroll + shift + enter:
(=SUM(--(B:B=B5)))
Andra funktioner med detta problem
Jag har inte verifierat detta själv, men det verkar som om flera funktioner har samma problem, inklusive SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF och AVERAGEIFS.
