we are using excel to do our capacity planning in the team. We have a bunch of people (P1-P3) that fill in a table. For each day they put if they work the entire day (1), work half-day (0.5) or are off (0). Some people have additional responsibilities and are not working 100% on our project. The percentage how much they work for are project is set in the %Work cell (0-1).

I'm looking for a way to calculate the total available capacity per week: For each week (mon-fri), for each person sum up the capacity for that week, multiply it with their %Work, then accumulate this over all people.

My initial approach was to create a hidden column for each week in which I sum up the raw capacity for each person, then use the sumproduct to multiply this cell with the %work and sum it up over all people. However, people kept mass deleting columns when they changed their plan, deleting the hidden columns as well.

Is there a way to do something like sumproduct in one step, but instead of choosing two columns, I choose one column and the sum of other columns: %Work * (M T W T F)

Example from the picture: P1 has 5 full "raw" working days in week 1, P2 has 4.5 "raw" days and P3 4 "raw" days. I want to multiply the 5 days with the 1 of P1 (=5 effective days), the 4.5 with 1 (=4.5 effective days) and the 4 with 0.5 (=2 effective days), according to the %Work column, add then sum up to a total of 11.5.

excel: Microsoft apps 365 for enterprise / V 2108