Question about counting recent cells

L

leafsfan1967

Let's say I've got several rows of data for one column updated each day
downward.

DAY 1
DAY 2
DAY 3
DAY 4
DAY 5
EMPTY
EMPTY
etc...

So each day, I add on so the next cell in the above example would be
DAY 6. Now, my dilema is that I only want to count the most recent 5
cells. For example, if I went all the way down to DAY 100, I would only
want to count DAY 96 through DAY 100. I have no idea how to use this
sort of concept in a sumif or countif formula.

Any help would be appreciated.
 
B

Biff

Hi!

What exactly do you want to do concerning the last 5 non-empty cells in the
column?

Count? Count what? What if there aren't 5 entries? Where is this data? What
cell does it start in?

Biff

"leafsfan1967" <[email protected]>
wrote in message
news:[email protected]...
 
L

leafsfan1967

I want Excel to sum up the last 5 cells in the particular column. There
will definitely be at least 5 cells of data but when I enter a 6th, I
only want it to sum cells 2-5. When I enter a 7th, I only want it to
sum cells 3-6. etc...
 
C

Cutter

One way:

Assuming data is in column A starting in row 2 and there is no numeric
data in that column below the value you last entered.

In A1 type:

=IF(COUNT(A2:A1000)<6,SUM(A2:A1000),SUM(OFFSET(A2,COUNT(A2:A1000)-{1,2,3,4,5},0)))

Note: I used the range A2:A1000 for your data - modify to meet your
needs.


PS - my condolences with regard to your choice of team ;-)
 
A

Alvin

cell a1 : "DAY 1"
cell a2 : "DAY 2" etc.
cell a50 : "DAY 50"

the formula will be :
=SUM(OFFSET(A1:A50,ROWS(A1:A50)-4,0,5,1))
 
Top