Using the SUMIF() function w/ #DIV/0! errors in range

**Pyrex238** · 04-19-2011, 02:34 PM

I have literally tried every formula I know of and criteria to ignore the #DIV/0! errors in the sum range, and I can't seem to figure anything out.

Here is my current formula:

=SUMPRODUCT(D5:D16,E5:E16)/SUM(SUMIF(E5:E16,">"&0))

I'm doing a weighted average of two columns which contain #DIV/0! errors in them (because they are yet to be calculated) and I want to add up the column and ignore or return #DIV/0! errors as zero. Both column D and E contain #DIV/0! errors. I'm not quite sure what is the best method for ignoring errors using the sumproduct formula. I've tried 4 different methods and none have worked for me.

What is the best approach to this? What am I doing wrong?

**Pete123abc** · 04-19-2011, 02:51 PM

Try this Pyrex238:
If there's an error, it will return a blank cell.
=IF(ISERROR(SUMPRODUCT(D5:D16,E5:E16)/SUM(SUMIF(E5:E16,">"&0))),"",SUMPRODUCT(D5:D16,E5:E16)/SUM(SUMIF(E5:E16,">"&0)))

or, change the "" to a "0" if you want a zero returned.

Pete

**Pyrex238** · 04-19-2011, 02:56 PM

Originally Posted by Pete123abc

Try this Pyrex238:
If there's an error, it will return a blank cell.
=IF(ISERROR(SUMPRODUCT(D5:D16,E5:E16)/SUM(SUMIF(E5:E16,">"&0))),"",SUMPRODUCT(D5:D16,E5:E16)/SUM(SUMIF(E5:E16,">"&0)))

or, change the "" to a "0" if you want a zero returned.

Pete

Well Pete, I appreciate the help. Unfortunately for some reason that returns nothing but a blank cell for me.

**bentleybob** · 04-19-2011, 03:07 PM

I would go to the source of your #DIV/0! errors and use IFERROR (formula, ""), which will show those missing calculations as blank cells and then (I believe) get treated as if they didn't exist. Also looks better!

**daddylonglegs** · 04-19-2011, 03:21 PM

You can use this "array formula" to ignore errors of any sort

=SUM(IF(ISNUMBER(D5:D16*E5:E16),D5:D16*E5:E16))/SUM(IF(ISNUMBER(E5:E16),E5:E16))

confirmed with CTRL+SHIFT+ENTER

**Pyrex238** · 04-19-2011, 03:31 PM

Originally Posted by daddylonglegs

You can use this "array formula" to ignore errors of any sort

=SUM(IF(ISNUMBER(D5:D16*E5:E16),D5:D16*E5:E16))/SUM(IF(ISNUMBER(E5:E16),E5:E16))

confirmed with CTRL+SHIFT+ENTER

Ah, the isnumber() works great. Now it's calculating without any problems. Thanks alot daddylonglegs!