La función SUMAPRODUCTO Cómo trabaja SUMAPRODUCTO

Título original: "Multiple Condition Tests"

Autores: Ken Wright y Frank Kabel
Publicado en http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Uso estándar de SUMAPRODUCTO

Pruebas de condiciones múltiples

SUMAPRODUCTO es una de las funciones más versátiles proporcionadas por Excel. SUMAPRODUCTO multiplica los
miembros correspondientes en series dadas; y devuelve la SUMA de sus productos.

Esta página habla del empleo clásico de SUMAPRODUCTO; y de cómo la creatividad y la flexibilidad de la que está
dotado le han permitido evolucionar a una función mucho más útil; y explica algunas técnicas utilizadas. Finalmente;
algunos ejemplos de SUMAPRODUCTO muestran su versatilidad.

Uso normal de SUMAPRODUCTO

Uso evolucionando de SUMAPRODUCTO
Ventajas de SUMAPRODUCTO
SUMAPRODUCTO Explicado
Formato de SUMAPRODUCTO
Ejemplos

Uso normal de SUMAPRODUCTO

En su formato clásico, SUMAPRODUCTO multiplica cada valor en una serie por el valor correspondiente en otra serie; y
devuelve el resultado sumado. Como un ejemplo; si las células A9:A11 contienen los valores 1; 2; 3 y B9:B11 contienen
10; 20; 30; entonces
=SUMAPRODUCTO(A9:A11;B9:B11)
devuelve 140; es decir: (1*10)+(2*20)+(3*30)=10+40+90=140.

Esta es una función útil; pero nada más. Más allá; el empleo 'más creativo' de SUMAPRODUCTO ha evolucionado, por
parte de personas que regularmente contribuyen en foros de discusión de Microsoft Excel. Esto ha sido un proceso
creativo y productivo que han incrementado significativamente el uso de SUMAPRODUCTO; pero de una forma que no
se encuentra en la ayuda de Excel.

Extendiendo el uso de SUMAPRODUCTO

En Excel; hay dos funciones muy útiles de apoyo en las funciones de SUMA y CONTAR condicionales: CONTAR.SI y
SUMAR.SI. Funciones muy útiles; pero limitadas ya que sólo pueden evaluar una sola gama de pruebas; y sólo una
condición de prueba.
Las condiciones múltiples son muy útiles para probar gamas (como por ejemplo, entre dos fechas); y pruebas dobles
(una serie = "A" y la otra = "B"); y mientras esto puede ser gestionado usando funciones matriciales,
"{=SUMA (SI (test_A; SI (test_B...}",
funciones "Ctrl+Shift" del tipo:
{=SUMA(SI($K$3:$K$179>=D21;SI($K$3:$K$179<=D22;$K$3:$K$179;0);0))} ; etc.; son complicadas, y
hay que usar una fórmula matricial.
Hay una alternativa mejor, usando SUMAPRODUCTO.
Observe que en esta sección, todas las fórmulas usan '*' (multiplicador) como formato de operador; pero esto supone
en sí mismo uno de los puntos de discusión acerca de la función de SUMAPRODUCTO. De ello se habla más adelante
con más extensión.

1/8
Para entender el uso de SUMAPRODUCTO; primero consideremos los datos siguientes:
A B C
1 Marca Mes Cantidad
2 Ford Junio 7500
3 Ford Junio 8300
4 Ford Mayo 6873
5 Ford Junio 11200
6 Renault Junio 13200
7 Renault Junio 14999
8 BMW Junio 17500
9 BMW Mayo 23500
10 BMW Junio 18000

Table 1.
Fácilmente podemos sumar el número de Ford =CONTAR.SI(A1:A10;"Ford"), que devuelve 4.
Asimismo, es posible conseguir el valor de los Ford vendidos, usando =SUMAR.SI(A1:A10;"Ford";C1:C10), lo que
da 33,873.-

¿Cómo conseguimos contar cuántos Ford se vendieron en junio, o el valor de esas ventas? Eso puede ser calculado a
través de:
{=SUMA(SI(A1:A10="Ford";SI(B1:B10;"junio";1;0);0)} ,
que es un fórmula matricial, obtenida con "Ctrl + Shift +Enter" y no sólo con "Intro".
De modo análogo; el valor de las ventas de "Ford", "hasta junio" es obtenido mediante:
{=SUMA(SI(A1:A10="Ford";SI(B1:B10;"junio";C1:C10;0);0)} , también un fórmula matricial.

Pero con SUMAPRODUCTO, la solución para el número de "Ford" vendidos en junio usando esta función es,
sencillamente:
=SUMAPRODUCTO((A1:A10="Ford")*(B1:B10;"junio")) , y el valor de sus ventas:
=SUMAPRODUCTO((A1:A10="Ford")*(B1:B10="Junio")*(C1:C10))
...
Bajo mi punto de vista , esta fórmula ofrece fácilmente el objetivo

Como una extensión adicional a ese uso, podemos añadir el signo más (+) al operador de las condiciones
"CONTAR.SI"; así como cuántas ventas eran "Ford" y fueron realizadas en junio. Esta fórmula indica cómo:
=SUMAPRODUCTO((A1:A10="Ford")+(B1:B10="Junio"))
A pesar de que se menciona formulas matriciales, aquí no se explican. En su lugar, visiten la página web de Chip
Pearson.

Tanto como quieran, tenemos una función versátil que puede realizar cualquier número de condiciones; y tiene una
flexibilidad tal que aporta extensibilidad. Su capacidad aumenta cuando se combina con otras funciones, como se
expone en los ejemplos que siguen.

Ventajas de SUMAPRODUCTO
Los test de condiciones múltiples son la mayor ventaja de la función SUMAPRODUCTO, tal como se ha descrito; pero
tiene además otras dos considerables ventajas. La primera es que puede trabajar con libros cerrados; y segunda, el la
capacidad de manejar valores de texto, que pueden ser retocados según se precise.
En el caso de trabajar con otro libro de trabajo, la función SUMAR.SI puede ser utilizada para calcular un valor, como
en el caso:
=SUMAR.SI('[Libro2.xls]RATES'!$K$11:$K$13;">1")
esta fórmula está bien, en sí; y el valor permanece aunque el otro libro ("Libro2.xls") se cierre; pero tan pronto como la
hoja se recalcula, la fórmula devuelve #VALOR! inmediatamente. SUMAPRODUCTO resuelve este problema; y a pesar
de que el libro [Libro2.xls] esté cerrado, la fórmula
=SUMAPRODUCTO(--('[Libro2.xls]RATES'!$K$11:$K$13>1);--('[Libro2.xls]RATES'!$K$11:$K$13))
funciona, aunque se introduzca la fórmula aún con el otro libro aún cerrado y aunque se recalcule la fórmula.
La segunda mayor ventaja se refiere a la capacidad de gestionar texto en columnas diferentes. Considérese el conjunto
de datos distribuidos de esta forma (tabla 2):
A B C
1 A B Item Number

2/8
 2 x 2 1
3 y 3 2
4 x 4 3
Tabla 2.
Si observamos las filas 1:4, podemos ver que tenemos un valor de texto en A1, que se trata simplemente de una
cabecera de fila, pero el principio sirve para texto en cualquier fila.

SUMAPRODUCTO puede devolver un error, o ignorar el texto; pero puede ser útil si queremos cálculos que eviten
errores, o si queremos que los descubra (y presumiblemente, corregirlos después).
Los errores pueden aparecer si usamos la opción, por ejemplo:
=SUMAPRODUCTO((A1:A4<>"x")*(C1:C4)), nos dará #¡VALOR!
Pero para ignorar o prevenir errores, podemos usar la versión modificada que usa el doble operador negativo ("- -", que
se explica más adelante):
=SUMAPRODUCTO(--(A1:A4="x");(C1:C4)), lo que nos aportará "1"

Cómo trabaja SUMAPRODUCTO

SUMAPRODUCTO Explicado
Comprender cómo trabaja nos ayudará a determinar dónde usarlo, cómo podemos construir la fórmula y cómo
ampliarla.
En las siguientes tablas muestran un conjunto de datos que vamos a utilizar. En el ejemplo, el problema es encontrar
cuantos vehículos de la marca "Ford" se vendieron, de categoría "A".

En la tabla 3, A9:A20 contiene la marca, B9:B20 muestra a categoría de los

A B C vehículos (clasificación A-D) y C9:C20 muestra las ventas de cada categoría y
9 Ford B 3 marca.
10 Vauxhall C 4
La fórmula para conseguir el resultado apetecido es:
11 Ford A 2
=SUMAPRODUCTO((A9:A20="Ford")*(B9:B20="A")*(C9:C20)) .
12 Ford A 1
La primera parte de la fórmula comprueba la matriz de marcas y chequea aquellas
13 Ford D 4
14 Ford A 3 cuyo valor es "Ford".
15 Ford A 2 Eso devuelve una matriz de valores tipo "TRUE/FALSE", que en este caso será:
16 Renault A 8 {TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}
17 Ford A 6 De forma análoga, la matriz de categorías es chequeada respecto al contenido
18 Ford A 8 cuyo valor es "A", siendo
19 Ford A 7 {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
20 Ford A 6 Finalmente, los números (C9:C20) no son chequeados respecto a valor alguno;
Tabla 3 simplemente construye una matriz con los valores hallados, que serán
{3;4;2;1;4;3;2;8;6;8;7;6}
Así, disponemos ahora de tres matrices, dos de las cuales son de valores tipo "TRUE/FALSE" y la otra de números,
como se recoge en la tabla 4.
Y ahora viene lo interesante. SUMAPRODUCTO normalmente trabaja con matrices
A B C
de números; pero aquí tenemos dos matrices de valores "TRUE/FALSE", además
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
de la matiz de tipo numérica final.
11 TRUE * TRUE * 2 Mediante el uso del operador "*" (signo multiplicación) podemos obtener una lista
12 TRUE * TRUE * 1 de valores que pueden ser sumados ("operados"). El signo "*" tiene el efecto de
13 TRUE * FALSE * 4 "obligar" a esas dos matrices a usar valores matriciales de "uno" y "cero" , 1/0.
14 TRUE * TRUE * 3 Multiplicando "TRUE" por "TRUE" devuelve otra vez "TRUE", es decir, valor
15 TRUE * TRUE * 2 numérico "1" en la celda de resultados
16 FALSE * TRUE * 8 (podemos hacer la prueba de introducir la A B C D
17 TRUE * TRUE * 6 fórmula "=VERDADERO*VERDADERO" – 9 1* 0* 3 0
18 TRUE * TRUE * 8 equivalente de "TRUE"*"TRUE"- y 10 0* 0* 4 0
19 TRUE * TRUE * 7 podemos ver cuál es el resultado: 1). 11 1* 1* 2 2
20 TRUE * TRUE * 6 Cualquier otra combinación distinta aporta 12 1 * 1 * 1 1
Tabla 4 "0". Multiplicando la primera matriz de 13 1 * 0 * 4 0
valores "TRUE/FALSE" por la segunda 14 1 * 1 * 3 3
matriz de valores "TRUE/FALSE", obtendremos una matriz compuesta de 15 1 * 1 * 2 2
valores, que serán: 16 0 * 1 * 8 0
17 1* 1* 6 6
{0;0;1;1;0;1;1;0;1;1;1;1} .
18 1* 1* 8 8
19 1* 1* 7 7
20 1* 1* 6 6
35
Tabla 5
3/8
Esta nueva matriz de valores "1/0" es multiplicada por la matriz numérica de la columna "C", de ventas, para obtener
una nueva matriz que satisface las condiciones establecidas en el test de condiciones ("Ford" y categoría "A").
SUMAPRODUCTO luego, suma los elementos de esta matriz para ofrecernos el resultado.
La tabla 4 muestra los resultados tal como se presentan antes de que actúe el operador "*".
La tabla 5 ofrece una presentación virtual de los resultados parciales de la multiplicación de los equivalentes "1/0" y el
resultado parcial de la multiplicación en cada fila, resultado que se ve en la columna "D".
De ahí se puede observar cómo SUMAPRODUCTO llega al resultado solicitado, 35.

La tabla 6 muestra la misma disposición de valores numéricos sin el resultado virtual de la columna "D", sin tener en
cuenta las unidades vendidas. Esto es el uso de SUMAPRODUCTO como equivalente de la función CONTAR, que en este
caso sería el recuento de las casillas que satisfacen las condiciones, es decir, aportaría el
A B C resultado parcial de la fórmula
9 1* 0= 0 =SUMAPRODUCTO((A9:A20=A1)*(B9:B20="A"))
10 0 * 0= 0 Si ha podido de seguir la explicación de la forma como opera, puede haber pensado que en
11 1 * 1= 1
lugar de usar los operadores "*" para resolver las matrices, podíamos haber dejado la
12 1 * 1= 1
función "SUMAPRODUCTO" para simplemente usar la "SUMA" de los miembros de la matriz
13 1 * 0= 0
compuesta, es decir, sin tener que llegar a ningún producto.
14 1 * 1= 1
15 1 * 1= 1 Eso es perfectamente correcto, y perfectamente válido; SUMAPRODUCTO puede operar en
16 0 * 1= 0 una matriz simple (ponga 1,2,3 en las celdas A1,A2,A3, y luego inserte por ejemplo, la
17 1 * 1= 1 fórmula
18 1 * 1= 1 =SUMAPRODUCTO(A1:A3) en una casilla, y devolverá "6", de forma correcta).
19 1 * 1= 1 De hecho, sólo precisamos del operador "*" para obligar a las matrices a ser comprobadas
20 1 * 1= 1 para una condición partcular, y no lo necesitamos si no está sujeta a un test condicional.
8 Así, podemos usar
Tabla 6 =SUMAPRODUCTO((A9:A20="Ford")*(B9:B20="A");(C9:C20))
que usa sólo un aspecto "producto".
Advertencias:
Cuando se usa la función SUMAPRODUCTO, todas las matrices deben tener el mismo tamaño, como miembros
correspondientes de una matriz que es multiplicada por cada una de las otras.
Las matrices pueden ocupar el volumen completo de una columna, por ejemplo, no se admite "A:A", pero sí se admite
"A1:A65535" si es preciso; sin embargo, sí se acepta valores para toda una fila, por ejemplo, "1:1".
En la función SUMAPRODUCTO no se admite el uso de evaluaciones de una matriz de columna contra otra matriz de
fila. Deben ser todas de columna o todas de fila.

Formatos de SUMAPRODUCTO

En los ejemplos presentados, el formato ha sido:

=SUMAPRODUCTO((matriz1=condición1)*(matriz2=condición2)*(matriz3))
Como ya se ha mencionado, también pueden ser del tipo:
=SUMAPRODUCTO((matriz1=condición1)*(matriz2=condición2);(matriz3))
Recordaos que el operador "*" sólo se incluye para obligar las matrices condicionadas "TRUE/FALSE" a resolver un
valor de tipo "1/0", numérico y operable.
Como el uso de un operador aritmético obliga a los valores "TRUE/FALSE" a su conversión a "1/0", se puede utilizar
diferentes operadores y obtener el mismo resultado. Además, es también posible obligar a cada una de las matrices
condicionadas de forma individual mediante la multiplicación de cada una de ellas por 1
=SUMAPRODUCTO((matriz1=condición1)*1;(matriz2=condición2)*1;(matriz3))
o bien:
=SUMAPRODUCTO(1*(matriz1=condición1);1*(matriz2=condición2);(matriz3))
o elevándola a potencia 1:
=SUMAPRODUCTO((matriz1=condición1)^1;(matriz2=condición2)^1;(matriz3))
o simplemente, por la adición de "cero":
=SUMAPRODUCTO((matriz1=condición1)+0;(matriz2=condición2)+0;(matriz3))
o incluso, se puede usar la función "N":
=SUMAPRODUCTO(N(matriz1=condición1);N(matriz2=condición2);(matriz3))
Estos métodos difieren del operador "*" en que se aplican a matrices individuales, mientras que "*" opera entre dos
matrices.
Cualquiera de esos métodos funciona cuando hay más de una matriz condicionada, así que lo que importa en cuanto a
las preferencias entre uno u otro es el uso. Si hay una única matriz condicionada, no se puede usar el operador "*"
puesto que no hay nada más con qué operar, por lo que cualquiera de los demás métodos sirve.

4/8
Aún otro método es usar el operador binario "- -", (operador "unario", o "doble resta", más bien) de esta manera:
=SUMAPRODUCTO(--(matriz1=condición1);--(matriz2=condición2);(matriz3))
El operador "doble resta" también obliga las matrices condicionadas al test, actuando de manera más acorde con el uso
clásico de SUMAPRODUCTO.

Hay mucha discusión acerca de la conveniencia de uno u otro operador, o si sobre si uno es más "estándar" que el
otro; pero en realidad, hay pocos casos en que un operador sea sensiblemente más apropiado que otro. Y acerca de la
discusión sobre los "estándares", todo es territorio por descubrir.
Para el autor, cree que es una cuestión de preferencias. Personalmente, está inclinado por la notación de la doble
resta, dado que evita la llamada a una función, funciona en todos los casos ("*" no opera en caso de una única
condición) y no le gustan las variantes de operaciones con "1" ('*1'; '^1'; o '+0'), así que su preferencia es
=SUMAPRODUCTO(--(matriz1=condición1);--(matriz2=condición2);(matriz3)) ,
que además tiene más similitud con el clásico SUMAPRODUCTO
Hay una última variante que se ha promovido últimamente, que consiste en el operador "resta simple" ("-")
=SUMAPRODUCTO(-(matriz1=condición1);-(matriz2=condición2);(matriz3))
Pero el autor no animaría mucho a ello, dado que en algún caso puede aportar un resultado negativo donde quizá no
debiera, sobre todo si el número de las matrices condicionadas es impar.
Así que, resumiendo
Los test de tipo A=10 normalmente se resuelven a "TRUE/FALSE" y cualquier operador se precisa solamente si se
desea obligar a una matriz a devolver valores "1/0", como
=SUMAPRODUCTO(--(B5:B1953=101))
Las matrices de SUMAPRODUCTO normalmente se separan mediante ";" [o la "coma" (",") dependiendo del separador
que se tenga vigente en el sistema]
=SUMAPRODUCTO(--(B5:B1953=101);--(C5:C1953=7))

Pero si se multiplica dos matrices de tipo "TRUE/FALSE", implícitamente resuelve a valores "1/0" que luego son
sumados; si no precisa de la ";" (o ","), entonces use "*":
=SUMAPRODUCTO((B5:B193=101)*(C5:C193=7))
Además, y para finalizar, matrices de valores pueden usar el mismo operador, o pueden volver a la "coma" (";" ó ",")
así que puede escribirse:
=SUMAPRODUCTO(--(B5:B1953=101);--(C5:C1953=7);(D5:D1953))
=SUMAPRODUCTO((B5:B1953=101)*(C5:C1953=7);(D5:D1953))
=SUMAPRODUCTO(--(B5:B1953=101);--(C5:C1953=7);--(D5:D1953))
=SUMAPRODUCTO((B5:B1953=101)*(C5:C1953=7)*(D5:D1953))
=SUMAPRODUCTO(--(B5:B1953=101);--(C5:C1953=7)*(D5:D1953))
Si el resultado es el producto de dos condiciones multiplicadas entre sí, entonces lo mejor es multiplicarlas
conjuntamente, para obligar a la conversión de valores "TRUE/FALSE" a valores "1/0" para permitir la suma.
=SUMAPRODUCTO((condición1)*(condición2))
De todas formas, si hay una sola condición, puede obligarse a "1/0" con la "doble resta" ("--")
=SUMAPRODUCTO(--(condición1))
Se puede obtener lo mismo igualmente con
=SUMAPRODUCTO((1*(condición1)))
y la anterior se puede obtener mediante:
=SUMAPRODUCTO(--(condición1);--(condición2))
No hay una situación en la que el autor sepa dónde la solución propuesta con "--" no pueda ser resuelta con "*".
Alternativamente, si se usa la función TRANSPONER dentro de SUMAPRODUCTO, debe usarse "*".
Así, puede verse que hay diversas posibilidades donde elegir. El autor deja la última palabra a Harlan Grove, que una
vez escribió esta párrafo en el que manifiesta preferir el signo "doble resta":
"...Como he referido anteriormente, lo que más me gusta del signo "doble resta" no es que disminuya la velocidad, sino
el hecho de que con la precedencia del operador "--" en 'Excel', es más difícil provocar errores tipográficos que con las
alternativas ^1; *1; +0.
Además como leo de izquierda a derecha prefiero escribir los signos de obligación a la izquierda mejor que a la derecha
de las expresiones booleanas, y "--" se ve mejor que 1* o que 0+... Incrustar expresiones booleanas dentro de la
función "N"() es otra alternativa, quizá más clara, pero eso supone llamadas de función anidadas, por lo que no lo uso.

5/8
Ejemplos
Buscando valores comparados en otro rango
Fechas en cualquier formato internacional
Usando la función TRANSPONER para comparar valores en una columna, no una columna
Chequear contra rangos múltiples no contiguos
Encontrar eventos de texto ignorando espacios de cabecera o finales
Find instances of a string; ignoring leading or trailing spaces
Suma de valores únicos en un rango
Evitar dobles recuentos en condicionados múltiples (no excluyentes)
Suma de items respecto una lista
Suma parcial de items en un rango
Suma entre dos fechas, excluyendo períodos de vacaciones
Sumar celdas visibles en un rango filtrado

Ejemplo 1.-
Contar el número de items en los que la fecha, en A42:A407 es anterior a la de hoy, y J42:J407 es igual a una matriz
variable de valores
Solución:
La fecha es suministrada por ($A$42:$A$407<HOY()). La matriz variable de valores está localizada en un rango y se
usa conjuntamente las funciones COINCIDIR y ESNUMERO:
=SUMAPRODUCTO((ESNUMERO(COINCIDIR(J42:J407;"Fred";0)))*($A$42:$A$407<HOY()))

Ejemplo 2.-
Contar el número de ventas en tres localizaciones de servicio desde un período de tiempo dado:
En sus elementos básicos, es un sencillo test de condición única. Si la fecha a comprobar contra la contenida en una
celda podría ser una sencilla:
=SUMAPRODUCTO((C5:C309>$A$1))*(H5:H309="A"))
Pero la fórmula muestra una técnica para usar formatos incluídos de fecha que funciona –tanto como el autor conoce-
en cualquier formato de fecha internacional que se use en Excel.
=SUMAPRODUCTO((C5:C309>(--("2004/05/31")))*(H5:H309="A"))

Ejemplo 3.-
En lugar de escribir criterios múltiples en la fórmula, ¿podemos tenerlas escritas en celdas y sólo referenciar las celdas?
La solución puede parecer un requisito para el que la solución
=SUMAPRODUCTO((B5:B63=L1:N1)*(C5:C63))
podía bastar.
Sin embargo, esto falla debido a que la solicitud de comparación de vectores requiere una columna en lugar de una fila,
por lo que se requiere la función "TRANSPONER" incorporada, con el siguiente resultado:
=SUMAPRODUCTO((B5:B63=TRANSPONER(P46:P48))*(C5:C63))
que, al incluir la función "TRANSPONER", debe ser incluída como una fórmula matricial.

Ejemplo 4.-
Originalmente tenía esto:
=SUMAR.SI(J2:J196;J209;L2:L196)
Pero necesitaba agregar rangos extra, con la misma condición definida. Los rangos serían:
R2:R196;U2:U196;V2:V196;Z2:Z196
Solución:
Esto puede ser resuelto mediante múltiples funciones SUMAPRODUCTO para los diferentes rangos, pero de forma un
poco ingenua. Puede ser resuelto con una única función SUMAPRODUCTO, incluyendo el operador "más (+)".
=SUMAPRODUCTO(--(J2:J196=J209);L2:L196+R2:R196+U2:U196+V2:V196+Z2:Z196)

Ejemplo 5.-
Hallar las ocurrencias de una determinada serie de caracteres, de valor "determinado" en el rango "A1:A100". Algunas
de las celdas de ese rango pueden incluir caracteres especiales al inicio o al final, o incluso espacios HTML.
Solución:
El recuento de a serie de caracteres es muy sencilla. Los espacios iniciales o finales pueden ser tratados con la función
"ESPACIOS" en la fórmula. De todas formas, "ESPACIOS" no maneja los espaciados HTML, que deben ser testeados – y
alterados- con la función "SUSTITUIR"
=SUMAPRODUCTO(--(ESPACIOS(SUSTITUIR(A1:A100;CARACTER(160);""))="determinado"))

6/8
Ejemplo 6.-
CONTAR EL NÚMERO DE VALORES ÚNICOS EN UN RANGO
Solución:
La primera versión funciona en el rango completo de A1:A20, siempre que no haya valores "vacíos" en las celdas.
=SUMAPRODUCTO(1/CONTAR.SI(A1:A20;A1:A20))
Per esto dará el error #DIV/0 si cualquiera de los valores del rango está en blanco, lo que obliga a la corrección para
evitar esos valores:
=SUMAPRODUCTO((A1:A20<>"")/CONTAR.SI(A1:A20;A1:A20&""))
Finalmente, para resolver el problema de intersección con el primer argumento "CONTAR.SI/SUMAR.SI"... que puede
dar problemas podemos añadir
=SUMAPRODUCTO((A1:A20<>"")/(CONTAR.SI(A1:A20;A1:A20)+(A1:A20="")))

Ejemplo 7.-
CONTAR EL NÚMERO DE VECES EN QUE SE CUMPLE UNA O VARIAS CONDICIONES EN DOS RANGOS
DIFERENTES
En este ejemplo, considere que en el rango "A1:A10" tenemos "países", que en el rango "B1:B10" tenemos
"continentes" al que pertenecen y que en el rango "C1:C10" disponemos el aviso de si son países pertenecientes al
"G7" o no ("Sí" o "No")
Solución:
Contar las veces que están en Europa o que pertenecen al "G7", podríamos usar:
=SUMAPRODUCTO((B1:B10="Europa")+(C1:C10="Sí"))
El problema está en la doble contabilización, puesto que hay países de Europa que pertenecen al G7. Este problema
Booleano de intersección puede ser resuelto mediante
=SUMAPRODUCTO((B1:B10="Europa")+(C1:C10="Sí"))-SUMAPRODUCTO(--(B1:B10="Europa");--
(C1:C10="Sí"))
que usa SUMAPRODUCTO para calcular los países que simultáneamente se encuentran en Europa y son a la vez del
G7; pero luego, localiza los que están en la intersección booleana y los resta, para evitar el recuento duplicado.

Ejemplo 8.-
CONTAR EL NÚMERO DE OCURRENCIAS DE MÁS DE UN VALOR EN UN RANGO DADO.
En nuestro ejemplo, contaremos cuántos "Ford" y "Chrysler" están en el rango "A1:A10"
La solución puede usar la condición "O" booleana del ejemplo anterior, pero en un rango único, como:
=SUMAPRODUCTO((A1:A10="Ford")+(A1:A10="Chrysler"))
Pero en este caso, buscamos dos valores en un rango único, y la ecuación puede mejorar con la comparación de
valores matriciales:
=SUMAPRODUCTO(--(A1:A10={"Ford";"Chrysler"}))

Ejemplo 9.-
Tenemos un rango de acciones numeradas en "A1:A10", correspondiendo a localizaciones en "B1:B10", y la
numeración en "C1:C10". Si precisáramos contar cuántos hay en un sitio en concreto de la numeración de la acción
podríamos usar directamente SUMAPRODUCTO; pero en este caso, el número de acción contiene determinada cadena
incluída en el número de acciones (supongamos que ésta sea "ATN")
Analicemos la fórmula propuesta:
=SUMAPRODUCTO(--(ESNUMERO(ENCONTRAR("ATN";A1:A10)));--(B1:B10="Barcelona");--(C1:C10))
La función SUMAR.SI puede usar comodines, pero sólo en un test; y SUMAPRODUCTO no soporta comodines
directamente.
La solución pasa por el uso de la función ENCONTRAR para averiguar si está o no la parte incluída de determinada serie
de caracteres en el número de acción. La función ENCONTRAR es sensible a mayúsculas o minúsculas; en caso que no
haga falta distinguir entre unas y otras, podemos usar la función HALLAR
La función ESNUMERO se usa para comprobar si la condición se cumple o no, evitando el temido error #VALOR!
Así pues, la solución propuesta es:
=SUMAPRODUCTO(--(ESNUMERO(HALLAR("ATN";A1:A10)));--(B1:B10="Barcelona");--(C1:C10))

7/8
Ejemplo 10.-
Contar el número de cierto día de la semana entre dos fechas; excluyendo los días festivos incluídos en ellas.
Solución:
SUMAPRODUCTO puede ser usado para calcular el número de un día particular de la semana entre dos fechas
Por ejemplo, asumamos que las dos fechas inicial y final estén situadas en las celdas A1 y A2; esta fórmula que se
expone devuelve el número de días "miércoles" entre dos fechas ("inicial" y "final")

A B C
1 inicial =SUMAPRODUCTO(--(DIASEM(FILA(INDIRECTO(A1&":"&A2)))=4))
2 final

Esta solución utiliza el hecho de que Excel guarda las fechas como series de números a partir de 1º de Junio de 1900;
las dos fechas pueden ser utilizadas en una función INDIRECTO para llevar virtualmente todas las fechas a filas.
Eso puede ser comprobado usando la función DIASEM y la función FILA para determinar si alguna de esas filas de
fechas contiene el día de semana en cuestión
Esto determina, desde luego, un límite para la última fecha, que es el 26 de junio de 2079, dado que Excel está
restringido a 65.336 filas
La función DIAS.LAB aporta la utilidad de excluir los días festivos en el recuento... De nuevo, podemos conseguir esto
mismo en nuestra función añadiendo la comprobación de festividades o vacaciones.
Asumiendo que las festividades se encuentran en un rango nombrado "festividades", podemos usar:
=SUMAPRODUCTO(--(DIAS.LAB(FILA(INDIRECTO(A1&":"&A2)))=4);--
(CONTAR.SI(festividades;FILA(INDIRECTO($A$1&":"&A2)))=0))

Podríamos también simular la función DIAS.LAB para hacer el recuento del número de días entre dos fechas,
excluyendo sábados, domingos y festivos usando:
=SUMAPRODUCTO(--(DIAS.LAB(FILA(INDIRECTO(A1&":"&A2)))<>1);
(--(DIAS.LAB(FILA(INDIRECTO(A1&":"&A2)))<>7));--
(CONTAR.SI(festividades;FILA(INDIRECTO($A$1&":"&A2)))=0))

Esto podría parecer innecesario, dado que podríamos obtener el mismo resultado más fácilmente con la función
DIAS.LAB; pero esta fórmula aporta una ventaja sobre la función DIAS.LAB.: no importa en qué orden estén las fechas;
y podría ser utilizada para excluir cualquier día de semana "1, 2, 3,..." y no solamente sábados y domingos, sólo
cambiando el valor del día de la semana.

Ejemplo 11.-
SUMAR SOLAMENTE LAS CELDAS VISIBLES QUE COINCIDEN CON DETERMINADO CRITERIO.
Por ejemplo, en un rango "A1:A100" de datos, suma todas las celdas que en "B1:B100", tienen un valor igual a "Norte"
En el rango, algunas filas no son visibles debido a la acción de un "Filtro" aplicado a los datos.

Se trata de sumar solamente las celdas visibles que coinciden con cierto criterio

Solución:
La solución saca partido de que la función ignora las celdas no visibles por la acción del "Filtro" aplicado
La primera parte es exactamente una comprobación condicional del rango "B1:B100", que han de coincidir con el valor
"Norte" y suma los valores correspondientes en "A1:A100":
--($B$1:$B$100="Norte");$A$1:$A$100
El recuento de las celdas visibles es más complejo. Como se ha mencionado anteriormente, usa "SUBTOTALES",
conjuntamente con las funciones FILA, INDICE y DESREF, tal como así:
--(SUBTOTALES(3;DESREF(INDICE($A$1:$A$100;1;1);FILA($A$1:$A$100)-
FILA(INDICE($A$1:$A$100;1;1));0))=1)

Así, pues, la fórmula de conjunto deviene:

=SUMAPRODUCTO(--(SUBTOTALES(3;DESREF(INDICE($A$1:$A$100;1;1);FILA($A$1:$A$100)-
FILA(INDICE($A$1:$A$100;1;1));0))=1);--($B$1:$B$100="North");$A$1:$A$100)

8/8