Do an array operation if it doesn't result in an error

[Jason]

If only it was as easy as the subject line makes it sound.


Here's the core of my problem:

I have three columns of data. I want to find (Col1/Col2)*Col3 for
each row and then sum the result. I can do this with the array
formula {=sum((Array1/Array2)*Array3)}. This works fine as long as
there are no errors (#N/A etc) in any of the arrays and as long as
Array2 has no zeros (else you get a divide by zero error).

Trouble is that my arrays will sometimes contain such values. I can
avoid the problem of errors if I do the (Col1/Col2)*Col3 for each row
in the array individually and them sum the results that are not
errors, but I cannot find a way to integrate this into the array
formula. Rather than checking for errors in each array I figure
it’s best to evaluate (Col1/Col2)*Col3 and then check for
errors, this way it incorporates the divide by zero scenario.

I have the feeling that I’m dancing around the solution using
IF, SUMIF, ISERROR() etc, but just can’t get the syntax
right…

Any thoughts are appreciated, thanks,
Jason.


example:

C1 C2 C3 C4
Array1 Array 2 Array3 =(1/2)*3
700 35 1 20
#N/A 65 1 #N/A
500 50 1 10
400 80 1 5
800 30 0 0
900 0 0 #DIV/0!
200 10 1 20
1000 120 0 0
500 25 1 20
200 100 1 2
___
Sum of Column 4 = 77 <- this is the number I'm after
===

 

[Aladin Akyurek]

=SUM(IF(ISNUMBER(A2:A8/B2:B8)*ISNUMBER(C2:C8),(A2:A8/B2:B8)*C2:C8))

which must be confirmed with control+shift+enter instead of just with enter.

Notice that the whole column references (e.g., A:A) are not allowed.

 

[Jason]

Thanks Aladin, works a treat. Wasn't able to nail the syntax through
guessing my way around.