I give up! Help Please...

D

Dino

I need to total a column but only the even numbered rows within tha
column. There are over 100 comulns so I can't enter them individuall
in the formula as it caps at 30. How do I formulate an array t
accomodate this?

Thanks :confused
 
R

Ron Coderre

This formula sums the values on even numbered rows in A1:A300:

=SUMPRODUCT(--(MOD(ROW($A$1:$A$300),2)=0)*$A$1:$A$300)

If that works...adapt it for the other columns.

Does that help?

Ro
 
D

Dino

I pasted your formula and excel accepted it yet my total shows " #value
and the cell is formatted correctly. There is also enough room in th
cell for any total to print. Those are the only inhibiting factors
can think of. Any ideas? By the way, I will need to modify an
working formula to do the same with the odd rows in a different total.

Thanks again..
 
R

Ron Coderre

Hmmm....It ought to work.

Two questions:

1)What's your data range and what sheet is it on?
2)What sheet and cell are you putting the formula into?

Regards,
Ro
 
R

Ron Coderre

Once more thing...I only get a #Value if one of the cells contains text
Is that your situation?

Ro
 
D

Dino

OK...

My data range is A4:A134. To accomodate the needed range, I change
your formula to read:


=SUMPRODUCT(--(MOD(ROW($A$4:$A$134),2)=0)*$A$4:$A$134)

I assume that it should have still worked. I am using sheet1, an
putting the forula into sheet 1, cell A136

I really appreciate you taking the time to help me here. I am not th
most computer savvy guy as I am sure is obvious
 
R

Ron Coderre

For now, I'll assume you have some text mixed into Cells A4:A134, s
here's your new formula:

=SUMPRODUCT(--(MOD(ROW($A$4:$A$134),2)=0)*IF(ISNUMBER($A$4:$A$134),$A$4:$A$134,0))

Note: Commit that array formula by holding down [Ctrl]+[Shift] an
hitting [Enter]

For odd numbered rows:
=SUMPRODUCT(--(MOD(ROW($A$4:$A$134),2)=1)*IF(ISNUMBER($A$4:$A$134),$A$4:$A$134,0))


Does that work?

Ro
 
D

Dino

No text in cells A4 - A134, all text is above those cells in th
heading. Only individual numbers, nothing over 20 in the individua
cells A4 - A134. This should be simple I'd think. If I just put in
simple sum formula for all cells A4:A134, it totals them all easil
but, all I need are the even cells' total omitting all odd cells.

The new formula does not work either. Thanks again
 
P

Peo Sjoblom

That's one of the blessings using sumproduct's built in format with the
unary minuses

=SUMPRODUCT(--(MOD(ROW(A4:A134),2)=0),A4:A134)

will disregard any text entries, only way it will return a value error is if
the there is an error in one of the cells

--
Regards,

Peo Sjoblom

(No private emails please)


"Ron Coderre" <[email protected]>
wrote in message
news:[email protected]...
 
D

Dino

Peo,

Thanks. I do not get an error message now but the total is Zero an
there are certainly values in the cells being totaled (assuming it i
totaling the even cell from A4:A134)

Thanks. If you have any more ideas, I'm wide open
 
D

Dino

The problem here is "user error". I am soory I am so stupid. I made
simple entry error and missed it numerous times. All is well. Ron'
original formula works fine. Sorry Ron for wasting time and thank
sooo much for your help. You too Peo.

I can have a cocktail, at last
 
Top