Need a Formuls

[Liverpool1959]

Hello
This should be easy for you guys!!

Column A - Row 1 will be employee name. Rows 2 and down will be days of the calendar. I want to enter a "1" if he worked and a "0" if he didn't work. At the bottom will be the Total. What I need is a formula to calculate how many days the employee has worked in the last 24. So when I enter todays number (either 1 or 0) it still only counts the bottom 24 cells with values in it.

Thanks

 

[elmer007]

I've assumed that you will be entering the 1's and 0's in column B, beginning in B2. The following formula should give you the 24-day total that you described. In my test table, I put in 50 days, so this formula is in cell B52 on my spreadsheet. You will need to adjust the ranges to the height of your table (i.e., change "B2:B51" to accomodate your range of dates).

In the bottom cell of column B: =IF(COUNT(B2:B51)<24,SUM(B2:B51),SUM(OFFSET($B$2,COUNT(B2:B51)-24,0,24,1)))


Let me know if this doesn't work or if you have any questions.

 

[Liverpool1959]

It adds alright. Until I enter the 25th entry. Then I get #ref# I also get a message about the formula containing circular info. And there is a blue line from the top to the bottom

 

[Liverpool1959]

My data goes from B3 to B52 with the formula in B53

=IF(COUNT(B3:B52)<24,SUM(B3:B52),SUM(OFFSET(#REF!,COUNT(B3:B52)-24,0,24,1)))

 

[elmer007]

It looks like it may have aquired the reference error when pasting in initially. The "#REF!" should be deleted and replaced with "B3". Let me know if this doesn't fix it or if there are still problems getting it to work properly.

Is the formula in B53 the whole time, or is it being cut/copied and pasted during the process? (trying to figure why the 25th entry is causing a problem- it's working in my test table)

 

[Liverpool1959]

I started over. It seems to work. One small problem remains. I dragged the formula all the way to column DI. All the Bs in the formula changed to the appropriate column, except $B$3. The B stays in the formula.
Here is what the formula looks like in column DH IF(COUNT(DH3:DH52)<24,SUM(DH3:DH52),SUM(OFFSET($B$3,COUNT(DH3:DH52)-24,0,24,1)))

See how the $B did not change like the rest of the formula. I will have to change this one character manually on each column

 

[elmer007]

In the formula that is in column B, removed the "$" from the reference. So change "$B$3" to "B3" and the formula will be able to adjust as you drag it across the sheet. The dollar signs create an absolute reference that doesn't change when a formula is copied, but they aren't necessary in your scenario so they can just be removed.

 

[Liverpool1959]

Success. Thanks for your help