Arrayformula Filter but with dynamic values Google Sheets

Question

Here is what I'm trying to do in this sheet: https://docs.google.com/spreadsheets/d/1uK7poWPaGvIR_v-7eiD84zdMXu6AFNJqSZWJff6FxiY/edit?usp=sharing

In "Task Tracker" tab, team members write tasks, hours they did in that task and a count of that task. In "Sheet1" tab, the tasks are automatically populated in the "Tasks This Week" section, and then those are chooseable as drop downs in the "Tasks" section. (This drop down is needed for something I'm building later to keep values in the cells consistent).

The trouble now is that I want to have the "Hours" and "Count" of Monday, Tuesday, Wednesday etc populate automatically based off of the values entered in the "Task Tracker" sheet. But, given that week-by-week, the values (name of tasks) may change, new tasks will arise, I don't have a static value I can filter by.

Is there a way I can use arrayformula+filter to do this, or another function? I want Monday, Tuesday, etc's "Hours" and "Count" in "Sheet 1" to populate automatically, tallying all of the values in the "Task Tracker" tab based off what task is selected in the drop down in the "Tasks" section of Sheet1.

is column A in task tracker sheet designated to have dates? otherwise where are the dates which can be converted to daynames and then distributed to your sheet1? — player0
There a multiple thinks that I'm not understanding. Your final goal is to have a table where each table that has the sum of hours and counts for each individual task for each working day, right? (basically the table in Sheet1) Also why do you have a dropdown? Couldn't it just be added in that table directly? — Martí

RalphD RalphD · Accepted Answer · 2021-01-26T14:11:32

We can simply take the value of the "Tasks" column and filter the values in our Task Tracker sheet with that specific task.

The rest is syntactic sugar to make sure we deal with empty cells, tasks that don't occur within the Task Tracker even once, and making sure the correct rows and columns are dynamic/static for easy adjustments as we fill out each day.

Here's the code:

=IF(
    ISBLANK($A6),
    "",
    IFERROR(
        SUM(
            FILTER('Task Tracker'!D$3:'Task Tracker'!D$32,
                   'Task Tracker'!$B$3:'Task Tracker'!$B$32 = $A6)
         ),
         0
    )
)

And here's the sheet: https://docs.google.com/spreadsheets/d/1yqg07r0q-2i24Wpv8AQhJjP39uynVBQCJGKEROsp6vM/edit?usp=sharing

Arrayformula Filter but with dynamic values Google Sheets

1 Answers