En este tutorial, aprenderá a hacer una fórmula SUMAR.SI o SUMAR.SI.CONSENTIDA entre mayúsculas y minúsculas en Excel y Google Sheets.
Las funciones SUMAR.SI y SUMAR.SI.CONJUNTO están disponibles tanto en Microsoft Excel como en Google Sheets. Y en ambas aplicaciones, no distinguen entre mayúsculas y minúsculas por naturaleza. Para sumar celdas de forma condicional que traten letras minúsculas y mayúsculas como caracteres diferentes, tendrá que pensar en otra cosa.
¿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 mayúsculas y minúsculas. Para asegurarse de esto, considere este ejemplo simple:
Supongamos 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 se encuentran en la columna B. El objetivo es obtener la suma de las ventas de un artículo específico, 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 obtener 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 considerando mayúsculas y minúsculas, puede usar las funciones SUMAPRODUCTO y EXACTA juntas:
SUMAPRODUCTO(–(EXACTO(criterios, rango)), rango_suma)
Con la identificación de destino en E1 (criterios), la lista de artículos en A2: A10 (rango) y los números de ventas en B2: B10 (suma_rango), las fórmulas toman estas 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, incluido el 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 suma los productos y genera la suma.
Suma si sensible a mayúsculas y minúsculas en Excel (con múltiples criterios)
En caso de que busque 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(criterio1, rango1)), –(EXACTO(criterio2, rango2)), suma_rango)
Por ejemplo, para sumar las ventas de un artículo en particular (F1) en una región determinada (F2), la fórmula es la siguiente:
=SUMPRODUCT(--(EXACT(F1, A2:A10)), --(EXACT(F2, B2:B10)), C2:C10)
Fórmula ‘Suma si la celda contiene’ que distingue entre mayúsculas y minúsculas en Excel
En una situación en la que necesite sumar valores en una columna si una celda en otra columna contiene cierto texto como parte del contenido de la celda, use la función SUMPRODUCT junto con FIND:
SUMAPRODUCTO(–(ESNUMERO(ENCONTRAR(criterios, rango))), rango_suma)
Por ejemplo, para sumar las ventas del artículo en E1 (que puede coincidir con una celda completa en A2:A10 o ser solo 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 una combinación más de ESNUMERO/ENCONTRAR:
SUMAPRODUCTO(–(ESNÚMERO(ENCONTRAR(criterio1, rango1))), –(ESNÚMERO(ENCONTRAR(criterio2, rango2))), suma_rango)
Por ejemplo, para obtener una suma 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, Noreste o 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, haces «doble negación» (–) para convertir los valores lógicos en 1 y 0:
{1;0;0;0;1;0;1;0;0}
Finalmente, la función SUMPRODUCT multiplica los elementos de las dos matrices y genera 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 varias condiciones, 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 caracteres en mayúsculas y minúsculas. Así es cómo:
SUMAR.SI(Fórmula de matriz(EXACTO(criterio, rango)), VERDADERO, rango_suma)
O
SUMAR.SI(ArrayFormula(ENCONTRAR(criterio, rango)), 1, rango_suma)
Para nuestro conjunto de datos de muestra, las fórmulas reales son las siguientes:
=SUMIF(ArrayFormula(EXACT(E1, A2:A10)), TRUE, B2:B10)
=SUMIF(ArrayFormula(FIND(E1, A2:A10)), 1, B2:B10)
Cómo funciona la fórmula SUMIF/EXACT:
Para hacer las Hojas de cálculo de Google SUMAR.SI(rango, criterio, [sum_range]) reconoce mayúsculas y minúsculas, usamos la siguiente fórmula para el argumento de rango:
ArrayFormula(EXACT(E1, A2:A10))
ArrayFormula obliga a EXACT a comparar el valor de E1 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 SUMIF/FIND:
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 dejará de buscar 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, usa el número 1 como criterio de SUMIF y tiene el trabajo hecho 🙂
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 de Hojas de cálculo de Google en combinación con EXACTO o ENCONTRAR:
SUMAR.SI.CONJUNTO(rango_suma, ArrayFormula(EXACTO(criterio1, rango1)), VERDADERO, ArrayFormula(EXACTO(criterio2, rango2)), VERDADERO)
O
SUMAR.SI.CONJUNTO(suma_rango, ArrayFormula(ENCONTRAR(criterio1, rango1)), 1, ArrayFormula(ENCONTRAR(criterio2, rango2)), 1)
Como ves, 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)
Así es como puede crear una fórmula SUMIF o SUMIFS que distingue entre mayúsculas y minúsculas en Excel y Google Sheets. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsx)
(hoja en línea)