I have the following challenge: Every week i receive an excel file with upcoming tasks (table1) and would like to add up the time required for all morning and evening tasks separately per day.
Table 1: Planned tasks for the week
| Monday | Shift | Tuesday | Shift | Wednesday | Shift | Thursday | Shift | Friday | Shift |
|---|---|---|---|---|---|---|---|---|---|
| task 1 | morning | task 5 | evening | ||||||
| task 6 | evening | ||||||||
| task 5 | morning | task 2 | morning | task 1 | evening | ||||
| task 4 | evening | ||||||||
| task 6 | evening |
What i did so far is separately count the different tasks and multiply them each with the time value stored in table 2 in a hidden sheet.
Table 2: Required time per task
| Task | Time |
|---|---|
| Task 1 | 1.0 |
| Task 2 | 0.3 |
| Task 3 | 0.8 |
| Task 4 | 0.2 |
| etc. | etc. |
As the references in the formula are hardcoded this has the major drawback of me having to modify the formula every time for each day when a new task is added:
=SUM(COUNTIFS($A$5:$A$200;"task 1";$B$5;"morning") * Table2!$A$1 + SUM(COUNTIFS($A$5:$A$200;"task 2";$B$5;"morning") * Table2!$A$2 SUM(COUNTIFS($A$5:$A$200;"task 3";$B$5;"morning")*Table2!$A$3 + .....
What i would like to achieve is a dynamic lookup in table2 for the keyword and corresponding value. That way the formula would no longer need to be modified and only keywords and times would need to be added to table2.
Unfortunately, I did not manage to find a suitable example of something comparable and didnt come up with something working myself (cannot use VBA or Python unfortunately).
I would really appreciate if someone could give me a hint on how to do this (or tell me if it is not possible in excel).
I tried to use the MATCH function, but i honestly got really confused by the syntax.
1
If you have 365, this can be done with the newer functions:
=LET(
d, taskTbl,
tasks, WRAPROWS(TOCOL(d, 1), 2),
morning, FILTER(tasks, CHOOSECOLS(tasks, 2) = "morning"),
evening, FILTER(tasks, CHOOSECOLS(tasks, 2) = "evening"),
morningTimes, XLOOKUP(CHOOSECOLS(morning, 1), timeTbl[Task], timeTbl[Time], 0),
eveTimes, XLOOKUP(CHOOSECOLS(evening, 1), timeTbl[Task], timeTbl[Time], 0),
VSTACK({"Morning Times", "Evening Time"}, HSTACK(SUM(morningTimes), SUM(eveTimes)))
)
I Name‘d the first table taskTbl and the second timeTbl. That way when you add tasks to either table, the formula will not have to be edited.
I have also assumed that your tables only include the single week.
For the table you show in your question, the result of this formula:
Note that tasks that do not exist in timeTbl will return 0 for the time so are not included in the totals.
Algorithm
- Convert the table to a single column
- Then
WRAPROWSto create a two column table
- Then
- Use the
FILTERfunction to create two tables representing morning and evening XLOOKUPto obtain the relevant times
(note the zero’s for those tasks that don’t exist in your Table 2)- Return the sums of the morning and evening times separately