Sumar los valores mayores o menores en un rango Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

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.

 AB
1PaísImporte
2España600
3Mexico355
4Colombia220
5Peru224
6Chile550
7Argentina320
8Ecuador122
9Venezuela320
10Francia155

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
  1. Ir a la celda E1. O la celda deseada donde obtener el resultado.

  2. 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.

  3. Verificar como muestra 1.505 como la suma los 3 valores mayores del rango B2:B10 (600+550+355 = 1505)

    Hoja Excel con celda E1 con el valor 1505 obtenido de la función

    O también se puede insertar otra función como fórmula matricial.

  4. Ir a la celda F1. O la celda deseada donde obtener el resultado.

  5. 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.

  6. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control 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"))))}.

  7. Verificar como muestra la suma los 3 valores mayores del rango B2:B10, como en el caso anterior. 

Hoja Excel con celda F1 con el valor 1505 obtenido de la función matricial
  • VALORES MENORES
  1. Ir a la celda E2. O la celda deseada donde obtener el resultado.

  2. 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.

  3. Verificar como muestra 497 como la suma los 3 valores menores del rango B2:B10 (122+155+220=497).

    Hoja Excel con celda E2 con el valor 497 obtenido de la función

    O también se puede insertar otra función como fórmula matricial.

  4. Ir a la celda F2. O la celda deseada donde obtener el resultado.

  5. 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.

  6. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control 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"))))}.

  7. Verificar como muestra la suma los 3 valores menores del rango B2:B10, como en el caso anterior. 

Hoja Excel con celda F2 con el valor 497 obtenido de la función matricial
  • 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.

Nivel de dificultad: Avanzado Excel Avanzado

5 comentarios en “Sumar los valores mayores o menores en un rango Excel

  • 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á

  • 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.

Responder a Trucos y Cursos Cancelar respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *