need some formula help please

  • Thread starter Thread starter Gary Keramidas
  • Start date Start date
G

Gary Keramidas

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
 
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
 
and there were semicolons instead of commas. i changed them but still get
n/a for the result
 
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)
 
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)
 
Back
Top