I have a range of numbers that contain hours worked
I need to calculate the total of 20 days worked
not every cell in the range has a value, so I will need to keep adding vaules
till I reach 20 days worked
I have tried count, countif, but I cannot make it keep checking cells for values, and stop when it has 20 values to sum

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results mocked up manually, highlight the cells you're trying to automate.

Quote:

---

Originally Posted by JBeaucaire

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results mocked up manually, highlight the cells you're trying to automate.

---

Hi I have attached a file hope that it explains what I am looking for

Try this in T4, then copy to the right:

=SUM(OFFSET(S$6, , , , -10-COUNTIF(J6:S6, ""))) / 10

Hi
This is close to what I need, but it does not count back the 10 working days.
We have
16:s6 as the days that need to be checked for hours.
N6 does not have any hours so the formaula should count add from I6:s6

So try:

=SUM(OFFSET(S$6, , , , -10-COUNTIF(J6:S6, 0))) / 10

this is working for me. The issue is now how to add a new range if you need it as in my example

=SUM(OFFSET('warehouse-drivers'!S30,, -20-COUNTIF('warehouse-drivers'!C30:S30,"")))/20


the range of C30:S30 is a total of 17 days,, so I need to go to the previous month and look at an additiona 3+ days to get the total of the last 20 days worked.

the range I need to add to the formula is
'warehouse-drivers'!W5:AG5

thanks for your help with this I am much further along with your assistance.

Spanning raw data over multiple sheets in an incorrect data storage method. I'm not going to pursue that method with you.

I suggest you move your data storage process onto a single sheet. You're using COLUMNS in a way most people use rows since there are far more rows typically.

I would suggest you consider moving to a standard single-sheet data storage method, and then use a second sheet to draw information out of that single sheet.

Quote:

---

Originally Posted by JBeaucaire

Spanning raw data over multiple sheets in an incorrect data storage method. I'm not going to pursue that method with you.

I suggest you move your data storage process onto a single sheet. You're using COLUMNS in a way most people use rows since there are far more rows typically.

I would suggest you consider moving to a standard single-sheet data storage method, and then use a second sheet to draw information out of that single sheet.

---

Hi I have changed the layout as you suggested, please let me know if
I have the format correct. I am now dealing with #ref error

This is how I would do this: