I give up! Help Please...

  • Thread starter Thread starter Dino
  • Start date Start date
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
 
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
 
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..
 
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
 
Once more thing...I only get a #Value if one of the cells contains text
Is that your situation?

Ro
 
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
 
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
 
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
 
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]...
 
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
 
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
 
Back
Top