SUMAR.SI y SUMAR.FIS con distinción entre mayúsculas y minúsculas en Excel y Hojas de cálculo de Google

¿SUMIF / SUMIFS distingue entre mayúsculas y minúsculas?

No, no es. Tanto en Excel como en Google Sheets, ni SUMIF ni SUMIFS pueden reconocer letras mayúsculas. Para asegurarse de esto, considere este ejemplo simple:

Suponga que tiene una lista de ID en la columna A, donde las letras mayúsculas y minúsculas indican diferentes elementos. Las cifras de ventas correspondientes están en la columna B. El objetivo es obtener una cantidad de ventas para un artículo en particular, digamos A-01.

Con la identificación del objetivo en E1, construimos esta fórmula SUMAR.SI clásica:

=SUMIF(A2:A6, E1, B2:B6)

Y obtienes un resultado absolutamente incorrecto 🙁

Fórmula Sum If que distingue entre mayúsculas y minúsculas en Excel

Para sumar celdas con una condición que distingue entre mayúsculas y minúsculas, puede usar las funciones SUMPRODUCT y EXACT juntas:

SUMAPRODUCTO(–(EXACTO(Criterios, rango)), cantidad_rango)

Con ID de objetivo en E1 (Criterios), la lista de artículos en A2:A10 (rango), y números de ventas en B2:B10 (cantidad_rango), las fórmulas toman las siguientes formas:

=SUMPRODUCT(--(EXACT(E1, A2:A10)), B2:B10)

Si es necesario, puede «codificar» los criterios directamente en la fórmula:

=SUMPRODUCT(--(EXACT("A-01", A2:A10)), B2:B10)

Cómo funciona esta fórmula:

En el corazón de la fórmula, la función EXACTA compara el elemento de destino (E1) con cada elemento de la lista y devuelve VERDADERO si los valores comparados son exactamente iguales, incluidas las mayúsculas de texto, FALSO de lo contrario:

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

Un operador unario doble (–) convierte VERDADERO y FALSO en 1 y 0, respectivamente:

{1;0;0;0;1;0;1;0;0}

La función SUMAPRODUCTO multiplica los elementos de la matriz anterior por los elementos correspondientes en B2:B10:

SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})

Y debido a que multiplicar por 0 da cero, solo sobreviven los elementos para los que EXACTO devolvió VERDADERO:

SUMPRODUCT({250;0;0;0;160;0;170;0;0})

Finalmente, SUMPRODUCT agrega los productos y genera la suma.

Suma de Ifs con distinción entre mayúsculas y minúsculas en Excel (con múltiples criterios)

Si está buscando una fórmula SUMAR.S.I. que distinga entre mayúsculas y minúsculas con dos o más criterios, puede emular este comportamiento definiendo un par adicional de rango/criterio en otra función EXACTA:

SUMAPRODUCTO(–(EXACTO(criterio 1, intervalo 1)), –(EXACTO(los criterios2, intervalo 2)), cantidad_rango)

Por ejemplo, para sumar las ventas de un artículo específico (F1) en una región específica (F2), la fórmula es la siguiente:

=SUMPRODUCT(--(EXACT(F1, A2:A10)), --(EXACT(F2, B2:B10)), C2:C10)

Suma sensible a mayúsculas y minúsculas si la celda contiene fórmula en Excel

En la situación en la que necesita agregar valores en una columna si una celda en otra columna contiene texto como parte del contenido de la celda, use la función SUMPRODUS con FIND:

SUMAPRODUCTO(–(ESNUMERO(ENCONTRAR(Criterios, rango))) cantidad_rango)

Por ejemplo, para sumar las ventas del artículo en E1 (que puede coincidir con una celda completa en A2:A10 o simplemente ser parte de la cadena de texto), la fórmula es:

=SUMPRODUCT(--(ISNUMBER(FIND(E1, A2:A10))), B2:B10)

Para sumar celdas basadas en múltiples condicionesagregue otra combinación ESNUMERO/ENCONTRAR:

SUMAPRODUCTO(–(ESNUMERO(ENCONTRAR(criterio 1, intervalo 1))), –(ESNUMERO(ENCONTRAR(los criterios2, intervalo 2))) cantidad_rango)

Por ejemplo, para obtener un monto de ventas con dos condiciones (id de artículo en F1 y región en F2), la fórmula es:

=SUMPRODUCT(--(ISNUMBER(FIND(F1, A2:A10))), --(ISNUMBER(FIND(F2, B2:B10))), C2:C10)

Tenga en cuenta que la fórmula suma las ventas de un artículo específico en cualquier región «norte», como Norte, NORESTEo noroeste.

Cómo funciona esta fórmula:

Aquí, usamos la función FIND que distingue entre mayúsculas y minúsculas para buscar el elemento de destino (E1). Cuando se encuentra el elemento, la función devuelve su posición relativa en la cadena de origen, de lo contrario, un error #VALOR.

{2;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!}

La función ESNUMERO convierte cualquier número en VERDADERO y los valores de error en FALSO:

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

A continuación, haga «doble negación» (–) para forzar los valores booleanos en 1 y 0:

{1;0;0;0;1;0;1;0;0}

Finalmente, la función SUMAPRODUCTO multiplica los elementos de las dos matrices y da el resultado:

SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})

Si su fórmula procesa más de una condición, SUMPRODUCT multiplicará los elementos de tres o más matrices. En nuestro caso es:

=SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {1;0;1;1;1;1;0;1;0}, {250;155;130;255;160;280;170;285;110})

SUMAR.SI distingue entre mayúsculas y minúsculas en Hojas de cálculo de Google

Las fórmulas Sum If que distinguen entre mayúsculas y minúsculas que creamos para Excel también funcionarán en Hojas de cálculo de Google. Además, puede obtener la función SUMIF de Google Sheet para distinguir entre letras mayúsculas y minúsculas. Así es cómo:

SUMAR.SI(Fórmula de matriz(EXACTO(criterio, rango)), VERDADERO, cantidad_rango)

O

SUMAR.SI(Fórmula de matriz(ENCONTRAR(criterio, rango)), 1, cantidad_rango)

Para nuestro conjunto de datos de ejemplo, las fórmulas reales se ven así:

=SUMIF(ArrayFormula(EXACT(E1, A2:A10)), TRUE, B2:B10)

=SUMIF(ArrayFormula(FIND(E1, A2:A10)), 1, B2:B10)

Cómo funciona la fórmula SUMAR.SI/EXACTO:

Para hacer Google Sheets SUMIF (rango, criterio, [sum_range]) reconoce letras mayúsculas, usamos la siguiente fórmula para rango argumento:

ArrayFormula(EXACT(E1, A2:A10))

ArrayFormula fuerza EXACTAMENTE que el valor de E1 se compare con cada valor de A2:A10. Si se encuentra una coincidencia exacta, la fórmula devuelve VERDADERO, de lo contrario, FALSO.

En el rango de valores VERDADERO y FALSO, SUMAR.SI busca VERDADERO y suma los valores correspondientes en B2:B10. ¡Eso es todo!

Cómo funciona la fórmula SUMAR.SI/ENCONTRAR:

Aquí, usamos la combinación de ArrayFormula y FIND para buscar el valor objetivo (E1) en el rango A2:A10:

ArrayFormula(FIND(E1, A2:A10))

Si se usa por separado, la función FIND detendrá la búsqueda después de encontrar la primera coincidencia.

Siempre que se encuentre el valor objetivo, la fórmula devuelve 1 (que es su posición relativa en la cadena de búsqueda). Para las celdas donde no se encuentra el valor, un #¡VALOR! se devuelve el error.

Luego usas el número 1 como criterio SUMAR.SI y listo 🙂

SUMIFS distingue entre mayúsculas y minúsculas en Hojas de cálculo de Google

Para sumar celdas con múltiples condiciones en Hojas de cálculo de Google, puede usar las fórmulas SUMAPRODUCTO que distinguen entre mayúsculas y minúsculas que se analizan en la parte de Excel de nuestro tutorial, o SUMAR.SI.CONJUNTO en Hojas de cálculo de Google en combinación con EXACTO o ENCONTRAR:

SUMAR.SI(cantidad_rangoArrayFormula(EXACTO(criterio 1, intervalo 1)), VERDADERO, Fórmula de matriz (EXACTO (criterio 2, intervalo 2), VERDADERO)

O

SUMAR.SI(cantidad_rangoFórmula de matriz (ENCONTRAR (criterio 1, intervalo 1)), 1, fórmula de matriz (ENCONTRAR (criterio 2, intervalo 2)), 1)

Como puede ver, la lógica es la misma que con la función SUMAR.SI. La diferencia es que utiliza dos o más pares de rango/criterio.

Por ejemplo, para sumar las ventas del artículo en F1 y la región en F2, las fórmulas son:

=SUMIFS(C2:C10, ArrayFormula(EXACT(F1, A2:A10)), TRUE, ArrayFormula(EXACT(F2, B2:B10)), TRUE)

=SUMIFS(C2:C10, ArrayFormula(FIND(F1, A2:A10)), 1, ArrayFormula(FIND(F2, B2:B10)), 1)

libros de trabajo

SUMAR.SI / SUMAR.SI.CONT. mayúsculas y minúsculas en Excel (archivo .xlsx)
SUMAR.SI/SUMAR.FIS con distinción entre mayúsculas y minúsculas en Hojas de cálculo de Google (hoja en línea)