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.

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.

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: