Suma condicional con errores en Excel

sábado, marzo 31, 2007

Si el rango de la función SUMA incluye valores de error, por ejemplo #N/A (valor no disponible), el resultado de la función será también #N/A.
Por lo general lo que hacemos es investigar la celda o celdas que generan esos valores de error y corregirlas.
Pero a veces el rango reside en una hoja remota o sencillamente no queremos corregir estas celdas ya que el resultado, a pesar de ser #N/A, es válido.
Para sumar un rango que incluye valores #N/A sencillamente usamos SUMAR.SI

Dado este rango, por ejemplo



La fórmula SUMAR.SI(A1:A12;"<>#N/A") da el resultado correcto, 408.

Supongamos ahora que en nuestro rango tenemos varios tipos de errores. Por ejemplo, #N/A y también #¡DIV/0!. Por ejemplo



Cómo hacemos para sumar los valores que no son error?

Ya vimos que SUMAR.SI no acepta más de un criterio. Por lo general para sumar, contar, con más de una condición usamos SUMAPRODUCTO o fórmulas matriciales.

actualización de la entrada, siguiendo la observación de Vicente
Pero en este caso, tampoco SUMAPRODUCTO o funciones matriciales pueden ayudarnos. Esto se debe a que al multiplicar los elementos de las matrices entre si, incluimos valores #N/A o #¡DIV/0! en la operación y el resultado es, obviamente, #error!


En un caso de rango "mixto" como el nuestro, la única solución que encuentro es podemos usar esta función matricial (sugerida por Vicente en su comentario)

={SUMA(SI(ESERROR(A1:A12);"";A1:A12))}

o escribir una función definida por el usuario (UDF).

En un módulo del editor de Visual Basic escribimos esta función

Option Explicit

Public Function Sum_Err(rng)
Dim cell As Range, Count


Count = 0
For Each cell In rng
If Not IsError(cell.Value) Then
Count = Count + cell.Value
End If
Next

Sum_Err = Count

End Function

Para usar la función podemos usar el asistente de funciones, seleccionando la categoría "definidas por el usuario"



Seleccionamos el rango que queremos sumar



Y obtenemos el resultado esperado



Technorati Tags:

17 comments:

Vicente Soler 07 abril, 2007 05:27  

Felicidades por tu blog.

La siguiente fórmula matricial me ha funcionado:

={SUMA(SI(ESERROR(A1:A12);"";A1:A12))}

Saludos

Jorge L. Dunkelman 07 abril, 2007 08:53  

Hola Vicente,
gracias por la observación. He corregido la entrada.

Anónimo,  10 julio, 2007 22:02  

Hola Jorge,

Quisiera saber como sumar un determinado rango de celdas en macros, utilizando un rango indefinido... Es decir, el rango varia segun la cantidad de datos a sumar q haya en la hoja de calculo.

Desde ya muchas gracias,

Rodrigo Prado Torres

el_bufon_tito@hotmail.com

Jorge L. Dunkelman 10 julio, 2007 22:31  

Hola Rodrigo,
suponiendo que se trate de un rango continuo, por ejemplo en la columna C empezando en el celda C2, para determinar dinámicamente la última celda del rango tendrías que definir una variable, digamos Ult_Fila y calcular el número de fila. Sería algo así:

dim ult_fila as long

ult_fila = worksheetfunction.counta(range("C:C"))


Luego para calcular la suma usarías:

worksheet.sum(range([C2],cells(ult_fila,3))

Anónimo,  11 julio, 2007 23:19  

Jorge,

gracias por la ayuda, ya que he utilizado la variable long con exito; sin embargo, no puedo efectuar la suma:

Sheets("Amex").Select
Dim ult_fila As Long

ult_fila = WorksheetFunction.CountA(Range("N:N"))

Range("N2").Select

Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select ' aca necesitaria poner el resultado de la suma de ese rango

Worksheets.Sum (Range([N2], Cells(ult_fila, 14))) ' aca me dice q no se encontro el metodo o el dato miembro

End Sub

Desde ya muchas gracias, y aprecio la ayuda que me estas dando,


Rodrigo Prado Torres

Jorge L. Dunkelman 12 julio, 2007 13:36  

Hola Rodrigo
la expresión Worksheets.Sum tiene que estar ligada a una variable o a un rango.
Te propongo que sigas la consulta por mail privado conmigo (jorgedun@gmail.com). Si quieres puedes enviarme el archivo.

Anónimo,  12 julio, 2007 15:12  

Jorge:

¿cómo harías para realizar una suma con errores, sobre rangos separados?

Intenté con l afunción definida por el usuario que propones pero no me funciona, porque al poner Sum_Err (rango1;rango2;...;rangoN) me toma solamente el primero. No sé como hacer tampoco para que VB me reconozca N argumentos.

Jorge L. Dunkelman 12 julio, 2007 20:49  

La función funciona sólo con rangos contiguos. Es decir que podrías usarla si puedes generar un rango rectangular con os distintos rangos.

Anónimo,  02 enero, 2012 15:43  

Hola! está muy bueno tu blog felicitaciones!

Quiero preguntarte cómo puedo sacar el promedio sólo entre celdas que no sean igual a #¡div/0!.

En el rango deben estar los #¡div/0! por que si bien son error, en el futuro esas celdas cambiarán a un valor real ya que la planilla se completa diariamente con datos.

El resultado de la suma debe estar en un libro y cada una de las celdas a sumar están en otro libro y en hojas diferentes del libro.

He utilizado la función Promedio.Si, en donde le pongo como condicion "<>#!div/0", pero me dice que son muchos argumentos para la función.

Espero tu ayuda, este problemilla me está atrasando con una entrega en el trabajo.

de antemano ¡muchas gracias!

Nicolás Depaoli
deathpalomy@hotmail.com

Jorge L. Dunkelman 03 enero, 2012 08:30  

Nicolás

PROMEDIO.SI no acepta rangos discontinuos (o 3D) y de ahí el problema.
Si el error en el rango es del tipo #DIV/0!, puedes usar la función PROMEDIO directamente (ésta si acepta rangos tridimensionales).
Una recomendación: no pongas tu drección de correo electrponico en los comentarios.

AFAA84 08 enero, 2013 20:10  

Hola, tengo un problema con calcular un promedio: tengo una columna que tiene una especificación, por ejemplo niños y niñas y en otra columna sus edades. niños y niñas están mezclados y no se pueden ordenar y necesito calcular sus edades.
Me podrían ayudar?

Jorge L. Dunkelman 08 enero, 2013 21:55  

Bien, en Excel 2010 tienes PROMEDIO.SI (una condición) y PROMEDIO.SI.CONJUNTO (más de una condición). En Excel Clásico (97-2003) tenemos las funciones base de datos y también las técnicas que muestro en esta nota.

drelo 28 junio, 2013 18:44  

Hola Jorge,

He intentado con la fórmula que dice Vicente y en mi caso no me ha resultado. Yo la quiero aplicar para cuando replique en toda mi matriz considere sumar en caso haya errores por ejemplo tengo filas con datos como:

0 0 0 1 0 1 #¡VALOR! #¡VALOR! 0


#¡DIV/0! #¡DIV/0! 0 0 0 1 1 0 0

y mis sumas son de manera horizontal así que quiero colocar la fórmula que comenta Vicente, pero no me ha dado valores.

Gracias por la ayuda y muy útil el post!.

Saludos

Andrés

drelo 28 junio, 2013 19:05  

Jorge,

Tengo una duda en la cual me gustaría tu opinión:

Tengo una matriz con las características que te comenté en mi pgta anterior y con el sgte encabezado:

Compra Venta Compra Venta Compra Venta

1 0 1 0 1 0

#¡DIV/0! #¡DIV/0! 0 0 0 1

0 1 0 #¡VALOR! #¡VALOR! 0

Como puedo hacer si quiero hacer las sumas horizontales considerando los errores y filtrando por el total compra y el total venta.

Gracias por tu ayuda!

Saludos

Jorge Dunkelman 29 junio, 2013 21:29  

La función de Vicente es una fórmula matricial que se ingresa apretando simultáneamente Ctrl-Mayúsculas-Enter

Anónimo,  17 febrero, 2015 15:42  

Gracias, funcionó. Muy buen aporte.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP