Formula generica
SUMPRODUCT(--(A:A=A1))
Sommario
Prefazione
Questa è un'introduzione fastidiosamente lunga, ma il contesto è importante, scusa!
Se provi a contare numeri molto lunghi (16+ cifre) in un intervallo con CONTA.SE, potresti vedere risultati errati, a causa di un bug nel modo in cui alcune funzioni gestiscono numeri lunghi, anche quando quei numeri sono memorizzati come testo. Considera la schermata qui sotto. Tutti i conteggi nella colonna D non sono corretti, sebbene ogni numero nella colonna B sia univoco, il conteggio restituito da CONTA.SE suggerisce che questi numeri sono duplicati.
=COUNTIF(data,B5)
Questo problema è correlato al modo in cui Excel gestisce i numeri. Excel può gestire solo 15 cifre significative e se inserisci un numero con più di 15 cifre in Excel, vedrai le cifre finali convertite silenziosamente a zero. Il problema di conteggio sopra menzionato nasce da questo limite.
Normalmente, puoi evitare questo limite inserendo numeri lunghi come testo, iniziando il numero con una virgoletta singola ('999999999999999999) o formattando le celle come Testo prima di inserirlo. Finché non è necessario eseguire operazioni matematiche su un numero, questa è una buona soluzione e consente di inserire numeri extra lunghi per cose come numeri di carte di credito e numeri di serie senza perdere alcun numero.
Tuttavia, se provi a utilizzare CONTA.SE per contare un numero con più di 15 cifre (anche se memorizzato come testo) potresti vedere risultati inaffidabili. Ciò accade perché COUNTIF converte internamente il valore lungo in un numero ad un certo punto durante l'elaborazione, attivando il limite di 15 cifre descritto sopra. Senza tutte le cifre presenti, alcuni numeri potrebbero essere conteggiati come duplicati quando contati con COUNTIF.
Soluzione
Una soluzione è sostituire la formula COUNTIF con una formula che utilizza SUM o SUMPRODUCT. Nell'esempio mostrato, la formula in E5 è simile a questa:
=SUMPRODUCT(--(data=B5))
La formula utilizza l'intervallo denominato "dati" (B5: B9) e genera il conteggio corretto per ogni numero con SUMPRODUCT.
Spiegazione
Innanzitutto, l'espressione all'interno di SUMPRODUCT confronta tutti i valori nell'intervallo denominato "dati" con il valore dalla colonna B nella riga corrente. Ciò si traduce in una matrice di risultati VERO / FALSO.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Successivamente, il doppio negativo costringe i valori VERO / FALSO a valori 1/0.
=SUMPRODUCT((1;0;0;0;0))
Infine, SUMPRODUCT somma semplicemente gli elementi nell'array e restituisce il risultato.
Variante della formula di matrice
Puoi anche usare la funzione SUM invece di SUMPRODUCT, ma questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO:
(=SUM(--(B:B=B5)))
Altre funzioni con questo problema
Non l'ho verificato personalmente, ma sembra che diverse funzioni abbiano lo stesso problema, tra cui SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF e AVERAGEIFS.
