Fórmula genérica
SUMPRODUCT(--(A:A=A1))
Resumen
Prefacio
Esta es una introducción molestamente larga, pero el contexto es importante, ¡lo siento!
Si intenta contar números muy largos (más de 16 dígitos) en un rango con CONTAR.SI, es posible que vea resultados incorrectos debido a un error en la forma en que ciertas funciones manejan números largos, incluso cuando esos números se almacenan como texto. Considere la siguiente pantalla. Todos los recuentos de la columna D son incorrectos; aunque cada número de la columna B es único, el recuento devuelto por COUNTIF sugiere que estos números son duplicados.
=COUNTIF(data,B5)
Este problema está relacionado con la forma en que Excel maneja los números. Excel solo puede manejar 15 dígitos significativos, y si ingresa un número con más de 15 dígitos en Excel, verá los dígitos finales convertidos silenciosamente a cero. El problema de recuento mencionado anteriormente surge de este límite.
Normalmente, puede evitar este límite ingresando números largos como texto, ya sea comenzando el número con una comilla simple ('999999999999999999) o formateando la (s) celda (s) como Texto antes de ingresar. Siempre que no necesite realizar operaciones matemáticas en un número, esta es una buena solución y le permite ingresar números extra largos para cosas como números de tarjetas de crédito y números de serie sin perder ningún número.
Sin embargo, si intenta utilizar CONTAR.SI para contar un número con más de 15 dígitos (incluso cuando se almacena como texto), es posible que vea resultados poco fiables. Esto sucede porque CONTAR.SI convierte internamente el valor largo de nuevo en un número en algún momento durante el procesamiento, lo que activa el límite de 15 dígitos descrito anteriormente. Sin todos los dígitos presentes, algunos números pueden contarse como duplicados cuando se cuentan con COUNTIF.
Solución
Una solución es reemplazar la fórmula COUNTIF con una fórmula que use SUM o SUMPRODUCT. En el ejemplo que se muestra, la fórmula en E5 tiene este aspecto:
=SUMPRODUCT(--(data=B5))
La fórmula utiliza el rango con nombre "datos" (B5: B9) y genera el recuento correcto para cada número con SUMPRODUCT.
Explicación
Primero, la expresión dentro de SUMPRODUCT compara todos los valores en el rango nombrado "datos" con el valor de la columna B en la fila actual. Esto da como resultado una matriz de resultados VERDADERO / FALSO.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
A continuación, el doble negativo coacciona los valores VERDADERO / FALSO a valores 1/0.
=SUMPRODUCT((1;0;0;0;0))
Finalmente, SUMPRODUCT simplemente suma los elementos de la matriz y devuelve el resultado.
Variante de fórmula de matriz
También puede usar la función SUM en lugar de SUMPRODUCT, pero esta es una fórmula de matriz y debe ingresarse con control + shift + enter:
(=SUM(--(B:B=B5)))
Otras funciones con este problema
No lo he verificado yo mismo, pero parece que varias funciones tienen el mismo problema, incluidas SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF y AVERAGEIFS.
