I'm trying to automate my time tracking a bit more, and making it a bit more thorough.

Here's a link to my example that I'm working on

Basically, I want to add values in another tab based on the selection in the drop down menu, but filtered by specific weeks of the year.

Explanation:

• In the C or D column of tab "Top 2023", calculate minutes based on the minutes in column G and the drop down in column C tab "2023"
  Currently, I have that part working. I used SUMIF to look at column C on tab "2023", check if it matches "Work" (or "Off"), then add the hours based on either criteria.
• I want to then filter that data based on the week of the year, listed in columns A in both tabs
  I want to automate this so I don't need to touch it or manually select what rows are included, only to move the formula down. So if 3 rows on tab "2023" show week 1 in column A, then they're all added to C2 and D2 on tab "Top 2023", so on for the rest of the weeks of the year.

Is this possible? I was playing around with a few formulas trying to make it work (I'm still learning and researching!) in column F on the "Top 2023" tab, but couldn't quite figure it out.

I tried: My formula to calculate the hours from tab "2023" to tab "Top 2023" is =SUMIF('2023'!C3:C,"Work",'2023'!G3:G) which works fine.

I understand doing G3:G is going to grab the whole column, so I want to filter it down further without having to manually update the G3:G portion every week.

I tried this (I know it doesn't work, and I'm having hard time understanding if it will work): =ArrayFormula(sumifs('2023'!C3:C,"Work",'2023'!G3:G)(FILTER('2023'!A3:A)A2)) Which of course brings back an error, because it doesn't work like that.

CodePudding user response：

this is single cell formula drag-down and goes in cell C2:

=SUMIFS('2023'!G3:G,'2023'!C3:C,"Work",INDEX(WEEKNUM('2023'!B3:B)),WEEKNUM(B2),INDEX(YEAR('2023'!B3:B)),YEAR(B2))

this is arrayformula within C2 (auto-applies to whole column C):

=BYROW(B2:B,LAMBDA(bx,IF(bx="",,SUM(IFERROR(FILTER('2023'!G:G,'2023'!C:C="Work",WEEKNUM('2023'!B:B)=WEEKNUM(bx),YEAR('2023'!B:B)=YEAR(bx)))))))

• change 'Work' to 'Off' within the formula for Colum D (time off) values
• also took into account direct 'Date' Columns in both tabs instead of the helper columns created exclusively to extract WeekNumber.