Share via

using a sumifs formula to calculate data from the last 7 days with cell data conditions

Anonymous
2023-02-15T17:42:46+00:00

Hello i am creating a drivers hours of service spreadsheet that we can use for tracking purposes. I have been able to create a formula

=SUMIFS(DA39:FL39,DA2:FL2,"<=" &E1, DA2:FL2,">"&E1-7)

D39:FL39 are the hours worked

D2:FL2 is the date

E1 is a cell with the current date to make it all work

The formula works perfectly to capture the latest 7 days, however I would like it to incorporate a condition to only sum cells in "hours worked" beyond a cell containing a 0 value. as their hours reset after this.

for example

Wed 8 hours

Thurs 8 hours

Friday 8 hours

Sat 0 hours

Sun 0 hours

Mon 8 hours

Tues 8 hours

My current formula produces a result of 40 hours. The formula should produce a result of 16 hours as the worker has had at least a day off.

Any help is appreciated

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-03-01T08:23:45+00:00

i appreciate it, sorry for the delay. my computer at work wont allow file drop sites. had to get home before i could upload

*Our Expert @*HansV MVP is already on this . . please excuse Me for jumping in here and sharing My inputs.

Image

In cell B3, please try: =SUMPRODUCT(D3:HT3,($D$2:$HT$2<=$C$2)*($D$2:$HT$2>$C$2-8)*(COLUMN($D$2:$HT$2)>MAX(($D$2:$HT$2>$C$2-8)*($D$2:$HT$2<=$C$2)*((D8:HT8="off")+ISNUMBER(SEARCH("!",D8:HT8)))*COLUMN($D$2:$HT$2))))

In cell B8, please try: =SUMPRODUCT(($D$2:$HT$2<=$C$2)*($D$2:$HT$2>$C$2-200)*(COLUMN($D$2:$HT$2)>MAX(($D$2:$HT$2>$C$2-200)*($D$2:$HT$2<=$C$2)*((D8:HT8="OFF")+ISNUMBER(SEARCH("!",D8:HT8)))*COLUMN($D$2:$HT$2)))) & " Days Since Last Reset"

The highlighted part in the above formulas is the edit I did.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2023-02-18T09:13:31+00:00

That would be

=SUMPRODUCT((DA2:FL2<=E1)*(DA2:FL2>E1-7)*(COLUMN(DA2:FL2)>MAX((DA2:FL2>E1-7)*(DA2:FL2<=E1)*(DA39:FL39=0)*COLUMN(DA2:FL2))))

Was this answer helpful?

1 person found this answer helpful.
0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2023-02-15T20:18:47+00:00

    Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-02-15T19:59:41+00:00

    Wow, thanks for responding so quickly. I have the formula I originally posted in the green square, I inserted your suggestion into the cell on column DA. Unfortunately it did not yield a result even with confirmation.

    Is there a count function that would yield a count of cells beyond the "0" cells? I could then plug that cell value into my original formula

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2023-02-15T18:27:19+00:00

    Try

    =SUMPRODUCT(DA39:FL39,(DA2:FL2<=E1)*(DA2:FL2>E1-7)*(COLUMN(DA2:FL2)>MAX((DA2:FL2>E1-7)*(DA2:FL2<=E1)*(DA39:FL39=0)*COLUMN(DA2:FL2))))

    If you don't have Microsoft 365 or Office 2021, you'll probably have to confirm the formula by pressing Ctrl+Shift+Enter.

    Was this answer helpful?

    0 comments