i have 2 formulas i would like to combine, unless somebody has a better
idea.

i want to sum every other row in a column
=SUM(IF(MOD(ROW(H$5:H$28),2)=0,H$5:H$28,0))
this will sum all even rows

but some may have n/a in them:
=SUM(IF(ISNA(H5:H28),0,H5:H28))
this will sum all rows that don't contain n/a

i am looking for a formula that will sum every other row, but skip n/a's.

any help appreciated


--


Gary

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

the formula will not even enter, gives me an error even when trying to enter
as an array

--


Gary


"abcd" wrote in message
...
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

sorry, translation error,
it's

SUMPRODUCT

and not sumprod

and there were semicolons instead of commas. i changed them but still get
n/a for the result

--


Gary


"abcd" wrote in message
...
sorry, translation error,
it's

SUMPRODUCT

and not sumprod

Hi Gary,

Try

=SUM(IF(MOD(ROW(H$5:H$28),2)=0,IF(NOT(ISERROR(H$5: H$28)),$H$5:$H$28),0))

This is an array formula, so commit with CTrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
and there were semicolons instead of commas. i changed them but still get
n/a for the result

--


Gary


"abcd" wrote in message
...
sorry, translation error,
it's

SUMPRODUCT

and not sumprod

check the excel help
or give us the exact new formula you'd tryed

the sumproduct function is supposed to work perfectly

It won't work with errors in this way, the H5:H28 will error out even though
the ISNA(H5:H28) will evaluate to False.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" wrote in message
...

check the excel help
or give us the exact new formula you'd tryed

the sumproduct function is supposed to work perfectly

hi bob:

that seems to work perfectly, thanks.

--


Gary


"Bob Phillips" wrote in message
...
Hi Gary,

Try

=SUM(IF(MOD(ROW(H$5:H$28),2)=0,IF(NOT(ISERROR(H$5: H$28)),$H$5:$H$28),0))

This is an array formula, so commit with CTrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" wrote in message
...
and there were semicolons instead of commas. i changed them but still get
n/a for the result

--


Gary


"abcd" wrote in message
...
sorry, translation error,
it's

SUMPRODUCT

and not sumprod