Usando SUMPRODUCT poder contar con múltiple o criterios
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.
Enlace genérico: / suma-excel-sumproducto-función [SUMPRODUCTO]
Fórmula para contar con múltiples criterios o
|
= |
Criterio1: este es cualquier criterio que devuelva una matriz de VERDADERO y FALSO.
Criterio 2: 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 el código del distribuidor o el año coincide con 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:
|
= |
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 «INKA» y 2016 como código y año respectivamente.
Estos registros se cuentan como 1. Y así es como obtenemos 7.
¿Cómo funciona?
Entonces, echemos un vistazo a cómo se resuelve la fórmula paso a paso, luego discutiré cómo funciona.
|
= |
|
1⇒`SUMPRODUCT*`(–\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;…}+\{FALSE;FALSE;FALSE;TRUE;TRUE;…})>0 |
|
2⇒`SUMPRODUCT*`(–\{1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0 |
|
3⇒`SUMPRODUCT*`(–(\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;…}) |
|
4⇒`SUMPRODUCT*`(\{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á, TRUE se trata como 1 y FALSE 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.
link: / tips-the-double-negatives-in-excel [Signos dobles negativos (-)] se utilizan para convertir valores booleanos en 1 y 0.
Entonces, cada valor VERDADERO en la matriz se convierte en 1 y FALSO en 0. En el paso final, el SUMPRODUCTO suma esta matriz y obtenemos nuestra respuesta como 7.
Agregar más o criterios para contar usando 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í:
|
= |
¡Sencillo! no es
Pero digamos que desea tener dos criterios del rango de Código *. 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á:
|
= |
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 largo. Veamos cómo podemos hacer que esto funcione.
Para que esta fórmula funcione, debe ajustar los criterios del código en link: / funciones-matemáticas-excel-transpose-function [función TRANSPOSE]. Esto hará que la fórmula funcione.
|
= |
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.
Artículos relacionados
link: / summing-excel-sumproduct-function [Cómo usar la función SUMPRODUCT en Excel]: Devuelve la SUM después de la multiplicación de valores en múltiples matrices en Excel. Esta función se puede utilizar para realizar varias tareas. Esta es una de las funciones más versátiles.
link: / counting-countifs-with-dynamic-criteria-range [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 vincular: / contando-contadores-con-o-para-varios-criterios [CONTAR.SI con OR para varios criterios]: Contar celdas que tienen varios criterios que coinciden con la función OR. Para poner una lógica OR en la función COUNTIFS, no necesitará utilizar la función OR.
link: / tips-using-the-if-and-or-functions-together-in-microsoft-excel-2010-2013 [Uso de las funciones IF con Y / OR en Microsoft Excel]: Estas funciones lógicas se utilizan para realizar cálculos de criterios múltiples. Con IF, las funciones OR y AND se utilizan para incluir o excluir coincidencias.
link: / logic-excel-or-function [Cómo usar la función OR en Microsoft Excel]: La función se usa para incluir todos los valores VERDADEROS en múltiples criterios. enlace: / fórmulas-lógicas-recuento-celdas-que-contienen-esto-o-aquello [Cómo contar celdas que contienen esto o aquello en Excel en Excel]: Para las celdas que contienen esto o aquello, podemos usar el Función SUMPRODUCTO. Así es como haces esos cálculos.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-your-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.
enlace: / fórmulas-y-funciones-introducción-de-función-vlookup [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. link: / tips-countif-in-microsoft-excel [Cómo usar]
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [Excel]
enlace: / tips-countif-in-microsoft-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.
link: / excel-formula-and-function-excel-sumif-function [Cómo utilizar la función SUMIF en Excel] | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.