# Need a Formuls

#### Liverpool1959

##### New Member
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.

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

Last edited:
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)))

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)

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

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.

Replies
1
Views
133
Replies
0
Views
911
Replies
4
Views
311
Replies
9
Views
360
Replies
1
Views
400

1,196,446
Messages
6,015,303
Members
441,887
Latest member
acquamarine

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back