Question about counting recent cells
 

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.


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=474644

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"
wrote in message
news:leafsfan1967.1wnyuc_1128909907.2647@excelforu m-nospam.com...

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.


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile:
http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=474644

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...


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=474644

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 ;-)


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=474644

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))

"leafsfan1967" wrote:


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.


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=474644