SUMIF in discontinuous range with possible #N/As

Hi, I have two related questions.

First, I would like to do a sum function but with cells that are not next to each other and may sometimes be #N/A. See below:

=SUMIF((A1,C1,E1),"<>#N/A")

Unfortunately this only seems to work if the range is continuous, such as when it is (A1:E1). Any suggestions?

Second, I'm doing something similar but with an vlookup array. See below:

{=SUMIF(VLOOKUP($A$1,A1:AZ8,{5,14,23,32},FALSE),"<>#N/A"}

This also doesn't work. Any recommendations?

Thank you!

Originally Posted by martindwilson

try this array entered formula
=SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),IF(ISNA(A1:E1),0,A1:E1))

Thanks. What does the "--" do that's in front of MOD?

Originally Posted by lesoies

Thanks. What does the "--" do that's in front of MOD?

Look at his signature!

You could use

Formula:

=SUM(IF(ISNUMBER(N(OFFSET(A1,0,{0;2;4}))),N(OFFSET(A1,0,{0;2;4}))))

Originally Posted by Bob Phillips

You could use

Formula:

=SUM(IF(ISNUMBER(N(OFFSET(A1,0,{0;2;4}))),N(OFFSET(A1,0,{0;2;4}))))

Thanks. This works well. Out of curiosity, why is N() necessary, for example in front of the offset? As I understand it, N just returns the number if it is a number, and the error if it is an error, so why not go directly with ISNUMBER(OFFSET ? I tried it and it didn't work, so it seems N() is necessary, but for what reason? Is it because due to programming limitations it needs to somehow "convert" from the array created by OFFSET?