SUMAPRODUCTO sobre rango de criterios

Hace unas semanas me dió por pensar si era posible replicar el comportamiento de las funciones de BASE DE DATOS (funciones BD) con otras funciones como SUMAPRODUCTO y este fue el resultado.

La idea es que a partir de la base de datos/tabla llamada 'TblVTAS' y de un rango con criterios múltiples, donde juegan los operadores lógicos Y-O, poder obtener un dato acumulado de aquellos registros que verifiquen dichos criterios...

Si aplicamos una función BDSUMA o BDCONTARA, en las celdas L7 y L8:
=BDCONTARA(TblVTAS[#Todo];"Comercial";$K$1:$M$4)
=BDSUMA(TblVTAS[#Todo];"Comercial";$K$1:$M$4)

Comprobamos que el resultado es perfectamente válido.
Pero, ¿y si queremos obtener el dato para una lista de años?, esto es, ¿si queremos que además de cumplir dichos criterios tengamos una nueva variable por el año?... En ese caso las funciones BD no nos pueden ayudar :O

Y aquí entran en juego funciones como SUMAPRODUCTO con un uso especial...
En primer lugar replicaremos el cálculo exacto de los criterios del rango.
Así en M11 escribiremos:
=SUMAPRODUCTO(TblVTAS[Ventas]*
(SI(TRANSPONER($K$2:$K$4)="";1;(TblVTAS[Comercial]=TRANSPONER($K$2:$K$4)))
*SI(TRANSPONER($L$2:$L$4)="";1;(TblVTAS[País]=TRANSPONER($L$2:$L$4)))
*SI(TRANSPONER($M$2:$M$4)="";1;(TblVTAS[Producto]=TRANSPONER($M$2:$M$4)))))


Con la primera alternativa empleando SUMAPRODUCTO conseguimos obtener tres vectores de VERDADEROS o FALSOS (1 y 0) que verificarían los criterios (uno por cada fila del 'Rango de criterios'), para finalmente multiplicarlos por el vector del campo de las 'Ventas'.

Muy importante la acción de TRANSPONER para que la operación se realice adecuadamente!!.

Claro está que llegamos al mismo resultado visto en la celda L8 empleando BDSUMA... todo un logro ;-)

El paso siguiente consiste en incorporar un nuevo criterio de 'Año' a la ecuación.
Recordemos, se deben cumplir las condiciones del 'Rango de criterios', y además que coincida con cada año de la lista dada:

Comprobamos, al ver la fórmula en M15:M21, que la esencia es la descrita previamente en la celda M11.
Escribimos en M15 y luego arrastramos:
=SUMAPRODUCTO(TblVTAS[Ventas]*(TblVTAS[Año]=$K15)*
(SI(TRANSPONER($K$2:$K$4)="";1;(TblVTAS[Comercial]=TRANSPONER($K$2:$K$4)))
*SI(TRANSPONER($L$2:$L$4)="";1;(TblVTAS[País]=TRANSPONER($L$2:$L$4)))
*SI(TRANSPONER($M$2:$M$4)="";1;(TblVTAS[Producto]=TRANSPONER($M$2:$M$4)))))


La diferencia es que incluimos en el producto de criterios de SUMAPRODUCTO el campo 'Año'...
Meta alcanzada!!. Sobre los criterios de nuestro rango hemos conseguido adicionar uno nuevo sobre los años de nuestra lista!!.

Una fórmula alternativa, más fiable y segura, especialmente para aquellos criterios que generan subconjuntos de registros coincidentes, sería:
=SUMAPRODUCTO(TblVTAS[Ventas]*(TblVTAS[Año]=$K15)*
(MMULT((SI(TRANSPONER($K$2:$K$4)="";1;(TblVTAS[Comercial]=TRANSPONER($K$2:$K$4)))
*SI(TRANSPONER($L$2:$L$4)="";1;(TblVTAS[País]=TRANSPONER($L$2:$L$4)))
*SI(TRANSPONER($M$2:$M$4)="";1;(TblVTAS[Producto]=TRANSPONER($M$2:$M$4))));SECUENCIA(COLUMNAS((SI(TRANSPONER($K$2:$K$4)="";1;(TblVTAS[Comercial]=TRANSPONER($K$2:$K$4)))
*SI(TRANSPONER($L$2:$L$4)="";1;(TblVTAS[País]=TRANSPONER($L$2:$L$4)))
*SI(TRANSPONER($M$2:$M$4)="";1;(TblVTAS[Producto]=TRANSPONER($M$2:$M$4)))));1;1;0))>0))


Lo importante de esta última fórmula es el uso de MMULT para obtener los parciales por fila de la matriz de condiciones:
=MMULT(matriz;SECUENCIA(COLUMNAS(matriz);1;1;0))
nos permite saber sencillamente, evitando duplicidades de cálculo, si fila por fila cumple una o varias de las condiciones/criterios dados...

Agradecer a Tim Heng y Liam Bastick de sumproduct.com las ideas aportadas.