Remember Me?

#1
April 12th 05, 04:35 PM
 shat Posts: n/a
How do i make a sum formula ignore #div/0! errors in the range

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????

--
shat

#2
April 12th 05, 04:52 PM
 Fredrik Wahlgren Posts: n/a

"shat" wrote in message
...
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????

--
shat

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

/Fredrik

#3
April 12th 05, 05:08 PM
 N Harkawat Posts: n/a

=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

"shat" wrote in message
...
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????

--
shat

#4
April 12th 05, 05:16 PM
 Duke Carey Posts: n/a

And even shorter:

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

"Fredrik Wahlgren" wrote:

"shat" wrote in message
...
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????

--
shat

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

/Fredrik

#5
April 12th 05, 06:01 PM
 frankt Posts: n/a

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.

"shat" wrote:

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????

--
shat

#6
April 12th 05, 06:16 PM

=SUMIF(Y4:Y204,"<#DIV/0!")

shat wrote:
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????

#7
April 22nd 06, 02:47 PM posted to microsoft.public.excel.worksheet.functions
 AG Posts: n/a
How do i make a sum formula ignore #div/0! errors in the range

Thx boss

=SUMIF(Y4:Y204,"<#DIV/0!")

shat wrote:
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????

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 2 February 23rd 05 12:14 AM Helen McClaine Excel Discussion (Misc queries) 1 January 22nd 05 04:09 PM ebuzz13 Excel Discussion (Misc queries) 2 January 20th 05 08:33 PM Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 09:02 PM superfooz74 Excel Discussion (Misc queries) 5 December 28th 04 12:40 PM

All times are GMT +1. The time now is 12:36 AM.