Remember Me?

 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

 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

 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

 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

 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

 Aladin Akyurek Posts: n/a =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???? 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????

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 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. Copyright ©2004-2019 ExcelBanter.