Función AGREGAR en Excel (sumar ignorando errores y filas ocultas)
Usa AGREGAR en Excel para sumar, promediar o sacar el máximo ignorando errores y filas ocultas, sin envolver todo en SI.ERROR. Guía con los códigos.
Imagina que cierras el informe del mes. La columna de ventas se alimenta de un BUSCARV que consulta otra hoja, y dos productos todavía no están dados de alta, así que sus celdas muestran #N/D. Escribes la suma al final, pulsas Entrar y el total completo se convierte en #N/D. Dieciocho números buenos, dos celdas vacías de significado, y todo el informe en rojo.
Ahí es donde entra la función AGREGAR de Excel: calcula sumas, promedios, máximos o mínimos y, en el mismo movimiento, se salta los errores y las filas ocultas que tú le indiques. Es la hermana mayor de SUBTOTALES, con más operaciones y mucha más tolerancia. En esta guía tienes el truco en dos números, un caso resuelto, la comparación con sus rivales, las dos tablas de códigos y los fallos típicos.

Plantilla relacionada: «Control de Ventas Diario». Un libro de Excel gratis con 3 hojas ya montadas, para que veas esta técnica funcionando.
⬇️ Descargar la plantilla (gratis)
¿Qué debes recordar de AGREGAR para escribirla sin consultar nada?
AGREGAR parece intimidante porque sus dos primeros argumentos son números y no palabras. Pero se aprende con una frase: el primer número dice qué calculas y el segundo dice qué ignoras. Con eso en la cabeza, esta lista cubre casi todo lo que necesitas saber antes de tocar el teclado.
- Dos formas de escribirla. Para las operaciones 1 a 13 usas la forma de referencia,
=AGREGAR(nº_función; opciones; ref1; [ref2]), y puedes añadir referencias extra hasta un máximo de 253. Para las operaciones 14 a 19 usas la forma matricial,=AGREGAR(nº_función; opciones; matriz; k), donde k es obligatorio. - Primer número, de 1 a 19: la operación. El 9 suma, el 1 promedia, el 4 saca el máximo y el 5 el mínimo.
- Segundo número, de 0 a 7: lo que se ignora. Si solo te quedas con dos, que sean el 6 (errores) y el 7 (errores y filas ocultas); con ellos resuelves alrededor del 90 % de los casos.
- Separador de argumentos: en una instalación en español va el punto y coma, no la coma. Si copias una fórmula de un foro y falla, empieza por mirar eso.
- AGREGAR no repara nada. La celda con #N/D o #¡DIV/0! sigue mostrando su error; la función solo lo deja fuera del cálculo.
- Orden de los números. Si los inviertes, el resultado carece de sentido o aparece un error. Piensa «qué» primero y «sin qué» después.
- Opción 0 no ignora errores. Solo afecta a subtotales y AGREGAR anidados dentro del rango. Para saltarte errores necesitas 6 o 7.
- No olvides k en 14-19. Sin el cuarto argumento Excel devuelve un error de fórmula.
- Excel 2010 como suelo. Está en Excel 2010, 2013, 2016, 2019, 2021 y Microsoft 365. En Excel 2007 o anteriores verás #¿NOMBRE?.
Para leer cualquier fórmula de AGREGAR que te encuentres, descomponla de izquierda a derecha. Tomemos =AGREGAR(4;6;C2:C50): el 4 es el máximo, el 6 manda saltarse errores y C2:C50 es el territorio donde se busca. Tres piezas, ninguna sorpresa. En la forma matricial se añade una cuarta: =AGREGAR(15;6;C2:C50;3) te daría el tercer valor más pequeño del rango, de nuevo sin contar celdas con error.
También conviene saber que puedes pasarle varias referencias separadas en la forma de referencia, por ejemplo dos columnas distintas, y que solo hacen falta rangos del mismo tipo de dato para que el resultado tenga sentido. Si dudas entre dos rangos, pruébalo primero con una operación sencilla como el 9 y comprueba el total a ojo.
Una cosa más sobre el trasfondo: AGREGAR sustituye lo que antes exigía combinar SUBTOTALES con una fórmula matricial de SI.ERROR. Si necesitas repasar esta última, tienes la guía de cómo usar SI.ERROR en Excel.
¿Cómo queda un informe con errores resuelto de principio a fin?
Sigamos con el informe del arranque. Es un caso inventado, pero reproduce lo que pasa en cualquier hoja con fórmulas de consulta. Estas son las etapas, de la primera tentativa a la fórmula definitiva.
- Primer intentoEn B2:B20 tienes 18 números y 2 celdas con #N/D. Escribes
=SUMA(B2:B20)y el resultado es #N/D. Con=PROMEDIO(B2:B20)pasa exactamente lo mismo: un solo error en el rango contamina todo. - El rodeo clásicoEnvolver cada celda con SI.ERROR, montar una fórmula matricial con SI y ESERROR, o añadir una columna auxiliar. Funciona, pero deja una hoja más pesada y más difícil de revisar.
- La solución en una líneaCon
=AGREGAR(9;6;B2:B20)obtienes la suma de los 18 números buenos, como si las celdas con error fueran transparentes (9 = suma, 6 = ignorar errores). - El promedioPara la media ignorando errores basta cambiar el primer número:
=AGREGAR(1;6;B2:B20). No hay que confirmar nada con Ctrl+Mayús+Entrar. Si quieres refrescar cómo se calcula la media a secas, mira cómo calcular la media en Excel. - Máximo y mínimoEn un cuadro de mando donde la columna C2:C50 puede fallar de vez en cuando,
=AGREGAR(4;6;C2:C50)devuelve el máximo y=AGREGAR(5;6;C2:C50)el mínimo, sin que un dato pendiente rompa el panel. - Se aplica un filtroAhora tu jefe filtra la tabla por un cliente. Con
=AGREGAR(9;5;D2:D100)la suma se recalcula solo con las filas visibles, también con las ocultas a mano con clic derecho. Para montar esos filtros, repasa cómo ordenar y filtrar en Excel. - Filtro y errores a la vezLa combinación de la que más partido sacarás en informes reales:
=AGREGAR(9;7;D2:D100)suma las filas visibles e ignora los errores que aparezcan entre ellas. - La segunda mejor venta de MadridCon la forma matricial:
=AGREGAR(14;6;C2:C50/(B2:B50="Madrid");2). B2:B50 es la región, C2:C50 las ventas y el 2 final pide el segundo mayor. Las filas que no son de Madrid devuelven #¡DIV/0! al dividir, y la opción 6 las descarta.
Fíjate en un detalle del recorrido: en ningún momento se ha corregido el origen del problema. Los dos #N/D siguen ahí, visibles en sus celdas, y eso es una ventaja. Cuando el producto que falta se dé de alta, el error desaparecerá solo y el total pasará a incluir esos dos importes sin que toques la fórmula. Tú decides si prefieres ver el error para detectar huecos de datos o esconderlo; AGREGAR solo evita que se propague.
Un uso complementario consiste en mantener dos totales a la vista: =SUMA(B2:B20) normal y =AGREGAR(9;6;B2:B20) al lado. Si el primero muestra error y el segundo un número, sabes de inmediato que hay datos pendientes; si ambos coinciden, la columna está limpia. Es una comprobación barata que evita sustos al enviar el informe.
=SUMA(B2:B20) devuelve #N/D, mientras que =AGREGAR(9;6;B2:B20) devuelve la suma de los 18 números.¿Tus columnas arrastran errores de fórmula? Nuestras plantillas ya traen los cálculos preparados para que un dato pendiente no tumbe el resumen. Son gratis.
Ver la plantilla de contabilidad →Las ocho dudas que suscita AGREGAR cuando la usas por primera vez
¿Qué significa realmente AGREGAR en Excel?
¿En qué se diferencia de SUMAR.SI.CONJUNTO?
¿Puedo imponer varias condiciones a la vez?
¿Por qué me sale #¡VALOR! o #¿NOMBRE?
¿Ignora las celdas vacías?
¿Responde bien a los filtros de una tabla de Excel?
¿Hay que confirmar con Ctrl+Mayús+Entrar la forma matricial?
¿Está en Excel Online y en la aplicación móvil?
¿AGREGAR, SUBTOTALES o SI.ERROR? Compara antes de elegir
Si ya conoces SUBTOTALES, piensa en AGREGAR como su versión ampliada. Las dos calculan ignorando filas ocultas o filtradas, pero AGREGAR hace cosas que SUBTOTALES nunca ha podido. Para ver la función clásica a fondo tienes la guía de subtotales en Excel.
| Característica | SUBTOTALES | AGREGAR |
|---|---|---|
| Operaciones disponibles | 11 (1-11, más 101-111) | 19 (1-19) |
| Filas ocultas por un filtro | Las ignora | Las ignora |
| Filas ocultas a mano | Según el código que uses (el grupo 100 y siguientes) | Las ignora con la opción adecuada |
| Valores de error | No los ignora | Los ignora con las opciones 2, 3, 6 y 7 |
| K-ésimo mayor o menor, percentiles y cuartiles | No | Sí |
| Matriz sin Ctrl+Mayús+Entrar | No | Sí |
| Disponible desde | Versiones muy antiguas de Excel | Excel 2010 en adelante |
Queda hablar de dónde vas a abrir el archivo. Como AGREGAR existe desde Excel 2010, funciona sin problema en Excel 2010, 2013, 2016, 2019, 2021 y Microsoft 365. Si el libro llega a alguien con Excel 2007 o anterior, la fórmula mostrará #¿NOMBRE? y no calculará. En entornos con versiones mezcladas es más prudente combinar SUBTOTALES con SI.ERROR, aunque quede menos elegante.
Y si trabajas también con Google Sheets, ten en cuenta que no tiene una función equivalente exacta a AGREGAR. Sí incluye SUBTOTAL, con códigos parecidos a los de Excel para filas filtradas, pero no existe una sola función que mezcle operación y opciones de ignorar errores. La alternativa habitual es envolver el rango con SI.ERROR dentro de una fórmula matricial, algo como =ARRAYFORMULA(SUMA(SI.ERROR(B2:B20;0))) (comprueba el nombre de las funciones en tu idioma). Si tu plantilla debe vivir en los dos programas, SI.ERROR de forma explícita es la apuesta segura, porque existe en ambos con el mismo comportamiento.
¿Dónde sacarle partido a AGREGAR dentro de tus propias hojas?
Esta función brilla en hojas que cambian a menudo, tienen fórmulas de consulta o se filtran con frecuencia. Estos son los lugares donde más rápido se nota la diferencia, con guías y plantillas para completar cada uno.
- Resúmenes que dependen de BUSCARV o ÍNDICE+COINCIDIR: cuando un dato aún no existe, el #N/D no tiene por qué llegar al total. Si necesitas la base, empieza por cómo usar BUSCARV.
- Tablas filtradas por cliente, mes o categoría: el total se recalcula con lo visible. Combínalo con subtotales si además quieres agrupar.
- Rankings con condición: el segundo o tercer valor de una región sin columnas auxiliares, apoyándote en la función K.ESIMO.MAYOR.
- Control de ventas y cuadros de mando: mira la plantilla de control de ventas para ver cómo se organizan los datos de origen.
- Contabilidad de un negocio pequeño: la plantilla de contabilidad para pequeña empresa reúne fórmulas pensadas para que un dato pendiente no estropee los resúmenes.
Un consejo para adoptarla sin sustos: no sustituyas todas tus SUMA por AGREGAR de golpe. Empieza por las celdas de resumen, las que ven otras personas y las que dependen de consultas que pueden fallar. En el resto de la hoja, SUMA y PROMEDIO siguen siendo más legibles para quien las abra después, y un error visible en una columna de trabajo es una señal útil, no una molestia.
Cuando entregues el libro a un compañero, añade una nota junto a la celda con un recordatorio como «6 = ignora errores». Los dos primeros argumentos son números sueltos y, pasados unos meses, nadie recuerda qué significaba cada uno. Un comentario de una línea te ahorra descifrar tu propia fórmula y evita que otra persona la «arregle» por no entenderla.
Tablas de números y opciones: el diccionario completo de AGREGAR
Aquí tienes las dos tablas de referencia. Guárdalas en favoritos y olvídate de memorizar. La primera recoge los 19 números de función con su equivalente clásico; la segunda, las 8 opciones de lo que se ignora.
| Nº función | Equivale a | Resultado |
|---|---|---|
| 1 | PROMEDIO | Media aritmética del rango |
| 2 | CONTAR | Número de celdas con valores numéricos |
| 3 | CONTARA | Número de celdas que no están vacías |
| 4 | MAX | Valor más alto |
| 5 | MIN | Valor más bajo |
| 6 | PRODUCTO | Multiplicación de todos los valores |
| 7 | DESVEST.M | Desviación estándar de una muestra |
| 8 | DESVEST.P | Desviación estándar de toda la población |
| 9 | SUMA | Suma de todos los valores |
| 10 | VAR.S | Varianza de una muestra |
| 11 | VAR.P | Varianza de toda la población |
| 12 | MEDIANA | Valor central del rango |
| 13 | MODA.UNO | Valor que más se repite |
| 14 | K.ESIMO.MAYOR | El k-ésimo valor más grande |
| 15 | K.ESIMO.MENOR | El k-ésimo valor más pequeño |
| 16 | PERCENTIL.INC | Percentil k, incluyendo los extremos |
| 17 | CUARTIL.INC | Cuartil k, incluyendo los extremos |
| 18 | PERCENTIL.EXC | Percentil k, sin los extremos |
| 19 | CUARTIL.EXC | Cuartil k, sin los extremos |
En el día a día manejarás sobre todo del 1 al 13, y de ellos casi siempre el 9, el 1, el 4 y el 5. Los números del 14 al 19 son los que obligan a la forma matricial, con su argumento k. Esa es la razón por la que, al escribirlos, la fórmula pide un cuarto dato.
La segunda tabla es la que convierte a AGREGAR en algo especial, porque define qué se queda fuera mientras calcula. Las opciones 0 a 3 omiten además los subtotales y AGREGAR anidados; de la 4 a la 7 eso no ocurre, y por eso las últimas son las más directas.
| Opción | Qué deja fuera del cálculo |
|---|---|
| 0 | Nada relevante; solo omite subtotales y AGREGAR anidados |
| 1 | Filas ocultas, además de las funciones anidadas |
| 2 | Valores de error, además de las funciones anidadas |
| 3 | Filas ocultas, errores y funciones anidadas |
| 4 | Absolutamente nada, ni siquiera las anidadas |
| 5 | Filas ocultas |
| 6 | Valores de error |
| 7 | Filas ocultas y valores de error |
Las opciones 0 a 4 solo importan si tu rango contiene subtotales o AGREGAR anidados, algo poco habitual en una hoja normal. Para casi todo lo demás, tu tabla de bolsillo es esta: 5 para filas ocultas, 6 para errores y 7 para ambas cosas.
Para fijar las tablas, pruébalas con tres lecturas rápidas. =AGREGAR(12;6;E2:E80) devuelve la mediana de E2:E80 sin tener en cuenta las celdas con error; útil cuando unos pocos valores extremos distorsionan la media. =AGREGAR(2;6;F2:F80) cuenta cuántas celdas numéricas válidas hay, de modo que restarlo del total te dice cuántos errores arrastra la columna. Y =AGREGAR(17;6;G2:G80;1) calcula el primer cuartil del rango, también sin los errores.
Si alguna vez dudas entre PERCENTIL.INC y PERCENTIL.EXC (números 16 y 18), la diferencia está en los extremos: la versión inclusiva admite que el percentil llegue hasta el valor mínimo y el máximo, mientras que la exclusiva los deja fuera de su rango válido. Lo mismo ocurre con los cuartiles 17 y 19. Para análisis cotidianos, las versiones inclusivas (16 y 17) son las que casi siempre encajan con lo que esperas.
Excel® es una marca registrada de Microsoft Corporation. Este sitio no está afiliado a Microsoft.