# SUM Ignore Cell Errors

#### ajones

##### Board Regular
I have a sumif like
=SUMIF(\$B\$24:\$B\$992,\$B11,F\$24:F\$992)

However there are some cells that have errors #N/A. I would like to still sum the numbers and just ignore the errors but can't seem to get that to work.

I tried a sum product but the errors still came though.

=SUMPRODUCT( (\$B\$24:\$B\$992=\$B10)*(F\$24:F\$992>0),F\$24:F\$992)

In an ideal world I would add anything like {>0, 0, <0}.

I am also looking at hiding the cell errors in the original data, but I am afraid that could be more complicated.

I tried a few searches related to this but did not find quite what I was after

thanks for the help

Alan

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Jonmo1

##### MrExcel MVP
Actually, correcting the Errors is not that difficult...

=IF(ISNA(formula),0,formula)

Where formula is your original formula (without the =)..

Example,
=VLOOKUP(A1,B:D,3,FALSE)

=IF(ISNA(VLOOKUP(A1,B:D,3,FALSE)),0,VLOOKUP(A1,B:D,3,FALSE))

Hope that helps.

#### ajones

##### Board Regular
Jonmo1,

Thanks for the reply however as I have some indirect functions and other things also going on I was reluctant to do what you mentioned.

I have done that many times before, but was trying not get the the formulas much longer.

The problem is you essentially dupe the formula and I did not want do that with all the indirect work I have.

thanks

Alan

#### DonkeyOte

##### MrExcel MVP
you could if you feel so inclined use a UDF to do the error handling

Code:
``````Function F_ERR(result as variant, def as variant)
Select Case IsError(result)
Case True
F_ERR = def
Case False
F_ERR = result
End Select
End Function``````

then in XL

=F_ERR(INDIRECT(....),0)

where INDIRECT fails 0 will be returned.

#### Jonmo1

##### MrExcel MVP
Never Mind, my thought using isnumber didn't work..

Last edited:

Replies
3
Views
190
Replies
1
Views
146
Replies
3
Views
291
Replies
5
Views
293
Replies
5
Views
2K

1,191,274
Messages
5,985,695
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back