Como he mencionado en muchos de mis blogs, un SUMPRODUCTO es una función muy versátil y se puede utilizar para múltiples propósitos. En este artículo, veremos cómo podemos usar esta función para contar valores con múltiples criterios OR.
Fórmula genérica SUMPRODUCT para contar con varios criterios o
| = SUMPRODUCTO (- (((criterios1)+(criterios2)+… )>0) |
Criterio1: Este es cualquier criterio que devuelva una matriz de VERDADERO y FALSO.
Criterio2: Este es el siguiente criterio que desea verificar. Del mismo modo, puede tener tantos criterios como desee.
La fórmula genérica anterior se modifica a menudo para adaptarse a los requisitos para contar con múltiples criterios de quirófano. Pero la fórmula base es esta. Primero veremos cómo funciona esto a través de un ejemplo y luego discutiremos otros escenarios en los que necesitará modificar un poco esta fórmula.
Ejemplo: contar usuarios si código de distribuidor o añoPartidos Utilizando SUMPRODUCT
Entonces aquí tenemos un conjunto de datos de vendedores. Los datos contienen muchas columnas. Lo que tenemos que hacer es contar el número de usuarios que tienen el código "INKA" o el año es "2016". Asegúrese de que si alguien tiene ambos (código como "inka" y año 2016) debe contarse como 1.
Entonces, aquí tenemos dos criterios. Usamos la fórmula SUMPRODUCT mencionada anteriormente:
| = SUMPRODUCTO (- (((Código = I3) + (Año = K3))> 0)) |
Aquí, el código y el año se denominan rangos.
Esto devuelve 7.
En los datos tenemos 5 registros de código INKA y 4 registros del año 2016. Pero 2 registros tienen tanto "INKA" y 2016 como código y año respectivamente. Estos registros se cuentan como 1. Y así es como obtenemos 7.
¿Como funciona?
Entonces, echemos un vistazo a cómo se resuelve la fórmula paso a paso, luego discutiré cómo funciona.
| =SUMPRODUCTO(- (((Código = I3) + (Año = K3))> 0)) |
| 1=>SUMPRODUCTO(- (({VERDADERO; FALSO; VERDADERO; VERDADERO; VERDADERO; VERDADERO;…} + {FALSO; FALSO; FALSO; VERDADERO; VERDADERO;…})> 0)) |
| 2=>SUMPRODUCTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCTO(- ({VERDADERO; FALSO; VERDADERO; VERDADERO; VERDADERO; VERDADERO; VERDADERO;…}) |
| 4=>SUMPRODUCTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
En el primer paso, el valor de I3 ("INKA") se compara con cada celda del rango de códigos. Esto devuelve una matriz de VERDADERO y FALSO. VERDADERO para cada partido. Para ahorrar espacio no he mostrado todo VERDADERO-FALSO. De manera similar, el valor de K3 (2016) se compara con cada celda en el rango de años.
En el siguiente paso, agregamos estas dos matrices que dan como resultado una nueva matriz de valores numéricos. Como sabrá, VERDADERO se trata como 1 y FALSO como 0 en Excel. Entonces, cuando se agregan VERDADERO y VERDADERO obtenemos 2 y el resto lo puede entender.
En el siguiente paso, verificamos qué valor es mayor que 0 en la matriz. Esto vuelve a convertir la matriz en una matriz falsa verdadera. Por cada valor 0 que obtenemos, False y el resto se convierten en verdaderos. Ahora el número de valores VERDADEROS en la matriz es nuestra respuesta. Pero, ¿cómo los contamos? Así es cómo.
Los signos dobles negativos (-) se utilizan para convertir valores booleanos en unos y ceros. Entonces, cada valor VERDADERO en la matriz se convierte en 1 y FALSO en 0.
En el paso final, SUMPRODUCT suma esta matriz y obtenemos nuestra respuesta como 7.
Agregar más o criterios para contar con SUMPRODUCT
Entonces, si necesita agregar más o criterios para contar, puede agregar criterios usando el signo + a la función.
Por ejemplo, si desea agregar otro criterio a la fórmula anterior para que agregue el número de empleados que han vendido más de 5 productos. La fórmula SUMPRODUCT simplemente se verá así:
| = SUMPRODUCTO (- (((Código = I3) + (Año = K3) + (Ventas> 5))> 0)) |
¡Sencillo! ¿no es así?
Pero supongamos que desea tener dos criterios de Código distancia. Digamos que quiere contar "INKB". Entonces como haces esto? Un método es utilizar la técnica anterior, pero sería repetitivo. Digamos que quiero agregar 10 criterios más del mismo rango. En tales casos, esta técnica no es tan inteligente para contar con SUMPRODUCT.
Digamos que tenemos datos organizados de esta manera.
Los códigos de criterios están en una fila I2: J2. La disposición de los datos es importante aquí. La fórmula SUMPRODUCT para la configuración de recuento de 3 criterios OR será:
| = SUMPRODUCTO (- (((Código = I2: J2) + (Año = I3: J3))> 0)) |
Esta es la fórmula SUMPRODUCT para contar con varios criterios cuando se escriben en una fila varios criterios de un rango.
Esto devuelve la respuesta correcta que es 10.
Si escribe cualquier año en J3, la fórmula también agregará ese recuento.
Se utiliza cuando los criterios están en una fila. ¿Funcionará cuando los criterios estén en una columna para el mismo rango? No. No lo hará.
En este ejemplo, tenemos varios códigos para contar, pero estos códigos de tipo están escritos en una columna. Cuando usamos la fórmula SUMPRODUCT anterior, obtenemos un error ans # N / A. No explicaremos cómo se produjo este error, ya que hará que este artículo sea demasiado extenso. Veamos cómo podemos hacer que esto funcione.
Para que esta fórmula funcione, debe ajustar los criterios del código en la función TRANSPONER. Esto hará que la fórmula funcione.
| = SUMPRODUCTO (- (((Código = TRANSPONER (H3: H4)) + (Año = TRANSPONER (I3: I4)))> 0)) |
Esta es la fórmula para contar con múltiples o condiciones en el mismo rango cuando los criterios se enumeran en una columna.
Así que sí, amigo, espero haber sido lo suficientemente claro y que tenga sentido. Espero que sirva a su propósito de estar aquí. Si esta fórmula no resolvió su problema, avíseme sus requisitos en la sección de comentarios a continuación. Estaré más que feliz de poder ayudarte de cualquier forma. Puede mencionar cualquier duda relacionada con Excel / VBA. Hasta entonces sigue aprendiendo, sigue sobresaliendo.
Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel. Esta función se puede utilizar para realizar varias tareas. Esta es una de las funciones más versátiles.
COUNTIFS con rango de criterios dinámicos : Para contar con rango de criterios dinámicos simplemente usamos la función INDIRECTO. Esta función puede
COUNTIFS con OR para varios criterios : Cuente las celdas que tengan varios criterios que coincidan con la función OR. Para poner una lógica OR en la función COUNTIFS, no necesitará utilizar la función OR.
Uso de IF con funciones Y / O en Microsoft Excel : Estas funciones lógicas se utilizan para realizar cálculos de varios criterios. Con IF, las funciones OR y AND se utilizan para incluir o excluir coincidencias.
Cómo utilizar la función OR en Microsoft Excel : La función se utiliza para incluir todos los valores VERDADEROS en varios criterios.
Cómo contar celdas que contienen esto o aquello en Excel en Excel : Para las celdas que contienen esto o aquello, podemos usar la función SUMPRODUCTO. Así es como haces esos cálculos.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo utilizar la función BUSCARV de Excel| Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar Excel Función COUNTIF| Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.