Excel dispone de la herramienta los 10 mejores de los filtros (que muestra los valores), pero en ocasiones nos interesa sumar los valores mayores o menores en un rango Excel.
En la hoja Excel disponemos de los datos en el rango A1:B10. La columna A incluye la referencia del país y en la columna B el importe de ventas y deseamos obtener la suma de los 3 países con importe mayor y menor.
| A | B | |
|---|---|---|
| 1 | País | Importe |
| 2 | España | 600 |
| 3 | Mexico | 355 |
| 4 | Colombia | 220 |
| 5 | Peru | 224 |
| 6 | Chile | 550 |
| 7 | Argentina | 320 |
| 8 | Ecuador | 122 |
| 9 | Venezuela | 320 |
| 10 | Francia | 155 |
Emplearemos las siguiente funciones anidadas:
- Función SUMAPRODUCTO. Multiplica los valores de las matrices suministradas y devuelve la suma de esos productos.
- Función K.ESIMO.MAYOR. Devuelve el k-ésimo mayor valor de un conjunto de datos.
- Función K.ESIMO.MENOR. Devuelve el k-ésimo menor valor de un conjunto de datos.
- Función FILA. Devuelve el número de fila de una referencia.
- Función INDIRECTO. Devuelve una referencia especificada por un valor de texto.
- VALORES MAYORES
Ir a la celda E1. O la celda deseada donde obtener el resultado.
Escribir:
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:3"))))B2:B10, representa el rango de valores y 1:3, el número de valores mayores a sumar.
Verificar como muestra 1.505 como la suma los 3 valores mayores del rango B2:B10 (600+550+355 = 1505).
O también se puede insertar otra función como fórmula matricial.
Ir a la celda F1. O la celda deseada donde obtener el resultado.
Escribir:
=SUMA(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:3"))))B2:B10, representa el rango de valores y 1:3, el número de valores mayores a sumar.
Pulsar la tecla F2, para editar la fórmula
Pulsar la tecla Ctrl y mantenerla pulsada Pulsar la tecla Mayús y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:3"))))}.Verificar como muestra la suma los 3 valores mayores del rango B2:B10, como en el caso anterior.
- VALORES MENORES
Ir a la celda E2. O la celda deseada donde obtener el resultado.
Escribir:
=SUMAPRODUCTO(K.ESIMO.MENOR(B2:B10;FILA(INDIRECTO("1:3"))))B2:B10, representa el rango de valores y 1:3, el número de valores menores a sumar.
Verificar como muestra 497 como la suma los 3 valores menores del rango B2:B10 (122+155+220=497).
O también se puede insertar otra función como fórmula matricial.
Ir a la celda F2. O la celda deseada donde obtener el resultado.
Escribir:
=SUMA(K.ESIMO.MENOR(B2:B10;FILA(INDIRECTO("1:3"))))B2:B10, representa el rango de valores y 1:3, el número de valores menores a sumar.
Pulsar la tecla F2, para editar la fórmula
Pulsar la tecla Ctrl y mantenerla pulsada Pulsar la tecla Mayús y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(K.ESIMO.MENOR(B2:B10;FILA(INDIRECTO("1:3"))))}.Verificar como muestra la suma los 3 valores menores del rango B2:B10, como en el caso anterior.
- OTROS EJEMPLOS
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:1")))). Suma el valor mayor del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:2")))). Suma los 2 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:3")))). Suma los 3 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:4")))). Suma los 4 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:5")))). Suma los 5 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:6")))). Suma los 6 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:7")))). Suma los 7 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:8")))). Suma los 8 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:9")))). Suma los 9 valores mayores del rango B2:B10.
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B10;FILA(INDIRECTO("1:10")))). Suma los 10 valores mayores del rango B2:B10.
Cambiando la función K.ESIMO.MAYOR por K.ESIMO.MENOR, obtendríamos la suma de los valores menores.
Excel Avanzado
Si se quieren sumar los tres valores mayores, y en la matriz, alguna columna tiene menos de esos tres valores, porqué por ejemplo aún no se han rellenado, sale un valor #¡NUM! . Se puede evitar y que salga el valor entero al menos de la suma de los dos mayores, por el momento, y cuando haya mas valores, ya discriminará
Hola Javier,
En efecto, se puede ocultar los mensajes de error de Excel anidando la expresión del ejemplo con la función lógica SI.ERROR.
Saludos.
Se pueden excluir ceros?
como hago para sumar unidades de una lista de productos dependiendo el rango de precios que deseo sumar, es decir deseo sumar unidades de productos que esten entre el rango de precios que deseo obtener informacion.
muchas gracias ya valide informaciòn
Hola! Gracias por el post. Tengo un a duda y no se como resolverla.
En tu ejemplo ¿Si quieres quitar del intervalo el valor de Colombia, como le pasas a la función K.ESIMO.MAYOR el rango de valores?
Quiero hacer esto pero no se establecerle el rango:
=SUMAPRODUCTO(K.ESIMO.MAYOR(B2:B3 B4:B10;FILA(INDIRECTO("1:3"))))
Misma duda, ¿cómo hacer que Excel entienda que hay un salto en B2:B3 B5:B10 porque no queremos incluir B4?
Hola Juan,
Puedes aplicar la función como: =SUMAPRODUCTO(K.ESIMO.MAYOR((B2:B10;B12:B13);FILA(INDIRECTO("1:1"))))
Saludos.
Hola. podrán orientarme? como realizo para que en una celda me sume los numeros hasta el 9 y en otra del 9 en adelante... es decir tengo que realzar una planilla que sume en una celda las 9 horas diarias trabajadas y en otra celda el resto si se trabajo 12 horas
Hola Eze,
Puedes aplicar: =SUMAR.SI.CONJUNTO(A1:A10;A1:A10;">0";A1:A10;"<9") y =SUMAR.SI.CONJUNTO(A1:A10;A1:A10;">=9").
Saludos