Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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???? thanks in advance -- shat |
#2
|
|||
|
|||
"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???? thanks in advance -- shat =IF(V4=0,IF(D4=0,0,SUM(E4/D4*X4)),SUM(W4/V4*X4)) /Fredrik |
#3
|
|||
|
|||
=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???? thanks in advance -- shat |
#4
|
|||
|
|||
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???? thanks in advance -- shat =IF(V4=0,IF(D4=0,0,SUM(E4/D4*X4)),SUM(W4/V4*X4)) /Fredrik |
#5
|
|||
|
|||
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???? thanks in advance -- shat |
#6
|
|||
|
|||
=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???? thanks in advance |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i make a sum formula ignore #div/0! errors in the range
Thx boss
"Aladin Akyurek" wrote: =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???? thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
How to make formula grow with the table | Excel Discussion (Misc queries) | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
How to make a formula display results only if value is greater tha | Excel Discussion (Misc queries) |