How to use array formula to sum columns, then rows, then column in an excel 3d array

Question

I'm building a scheduling tool in Excel. This comprises a list of tasks with start & end dates, and staff that can be assigned to each task.

In order to be able to output this data into our budgeting system, I need to be able to calculate the % of a staff members time which is booked within a given month (summed up across all tasks).

I'm able to calculate the amount of time a staff member has available, on a single given task, in a given month using the following formula:

=SUM(INDEX(WorkdayArrayMonCol,MATCH(B2,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2,WorkdayArrayDateCol))*TRANSPOSE('Staff Profile'!$B$13:$B$17))

This works great for a single task, but I need an equivalent which works across a series of tasks.

My attempt currently looks like this:

=IFERROR(MMULT(MMULT(TRANSPOSE(ROW(INDEX(WorkdayArrayMonCol,MATCH(B2:B12,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2:C12,WorkdayArrayDateCol)))^0),INDEX(WorkdayArrayMonCol,MATCH(B2:B12,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2:C12,WorkdayArrayDateCol))),'Staff Profile'!$B$13:$B$17),0)

+ctrl+shift+enter

The formula uses Index/Match to look up B2:B12 (task start date) and C2:C12 (task end date):

Using 0 (0/01/1900) if the task is out of range of the current month. These search a table WorkdayArray

with 6 columns, Date, Mon, Tue.... to Fri, which then returns a 2D array of the date range comprising values to be summed of how many of each weekday are within the range. This is necessary to accurately capture public holidays.

This 2D array is then multiplied by the staff members work availability, which is at 'Staff Profile'!$B$13:$B$17, each of the 5 values being 0>=>1 representing Mon-Fri. E.g if they worked half days Tuesday but full days the rest of the week, the 5 cells would look like {1,0.5,1,1,1}.

Using the technique posted here: How to use an array formula to return an array of sums in Excel I've managed to get it condensed down to a vector, but I can't get it to sum up the final total.

Using the formula above, the output is {8.5;4;2;2;0;0;0;0;0;0;1}, which is the available workdays for each task, however my attempts to sum this resulting data using the MMULT technique linked, SUMPRODUCT, SUM or SUMIF keep resulting in the same data being returned:{8.5;4;2;2;0;0;0;0;0;0;1}

Since this calculation is going to be repeated for each financial month (y), across each staff member (x) it's not feasible to do a 3D excel representation by using (z) sheets as each of x, y, & z are impractically large. I'd rather not use VBA, and I'm close enough it seems like it should be possible using formulas.

Thanks very much!

Edit: A one-sheet version re Zac's request (same formula, adjusted references):

Single sheet version:

.

I've selected 11 cells to illustrate the array result (what I want is the formula to output the sum of these values, 17.5).

Answer 1

I used a different approach to get the required result, i.e. this array formula:

=SUM(COUNTIFS(B2:B12,"<="&Table1[Date],C2:C12,">="&Table1[Date])*MMULT(Table1[[Monday]:[Friday]],P3:P7))

confirmed with CTRL+SHIFT+ENTER

The COUNTIFS part gives you a vertical vector representing how many times each date appears in the column B and C date ranges and then the MMULT part gives you another vertical vector with a value for each date based on the P3:P7 values

Those two vectors can then be multiplied and summed for the final result - 17.5 shown in P12