try SUMPROD

this multiply each argument (element by element) and finaly add all
the elementary results

for example SUMPROD({1;2;3};{1;2;3}) = 1*1 + 2*2 + 3*3


NOTICE: a logical result is not a number, so for example you must add
--ISNA(range)
( the double -- at the begining makes FALSE becomes the number 0 and
TRUE becomes the number 1; it's not the only way you may multiply the
logical by 1 for example TRUE*1=1 )

example:

=SUMPROD( --ISNA(A1:A6) ; --(MOD(A1:A6;2)=1) ; A1:A6)

notice here I make MOD = 1 so the rows 1,3, etc. (even) will be used