How do i make a sum formula ignore #div/0! errors in the range

G

Guest

i have a colomn where each line has uses the formula
=IF(V4=0,SUM(E4/D4*X4),SUM(W4/V4*X4))
at the bottom i am trying to sum the answers
=sum(y4:y204) but some of the lines return #div/0! because there is no
data on that line.

?How do you tell the sum to exclude the #div/0! errors and sum the others to
get a total????

thanks in advance
 
F

Fredrik Wahlgren

shat said:
i have a colomn where each line has uses the formula
=IF(V4=0,SUM(E4/D4*X4),SUM(W4/V4*X4))
at the bottom i am trying to sum the answers
=sum(y4:y204) but some of the lines return #div/0! because there is no
data on that line.

?How do you tell the sum to exclude the #div/0! errors and sum the others to
get a total????

thanks in advance
Click to expand...

=IF(V4=0,IF(D4=0,0,SUM(E4/D4*X4)),SUM(W4/V4*X4))

/Fredrik
 
N

N Harkawat

=SUM(IF(NOT(ISERROR(y4:y204)),y4:y204))
array entered (ctrl+shift+enter)
will exclude all entries which have any errors (N/A#,Div# etc) and sum the
rest
 
G

Guest

A better solution is to avoid the situation altogether. Modify the formulas
to look like this:

=IF(V4=0,if(D4=0,0,E4/D4*X4),if(V4=0,0,W4/V4*X4))

Now there will not be any #DIV0 and you can use a normal SUM.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to sum cells and ignore the #div/0! 's ? 2
how to sum cells and ignore the #div/0! 's ? 6
How to ignore #DIV/0! in Sumproduct formula 7
How do I sum cells that have values from a text formula? 4
how to disregard the DIV in a range 5
Formula to sum every 4th cell returns #DIV/0! error in some column 5
how do I clear #DIV/0!but leave the formula in cell 2
How do I get rid of the #DIV/O in a cell? 1

Top