excelexcel-formula

Spill formula to sumifs by row & col for only relevant months

I have a loosely formatted table that has People going across the top, tasks going down the rows, and a column for start date and column for End Date. The user will input expected hours in the given subtasks and it will rollup into the task itself. I was able to get a sumifs by row divided by the number of months being looked up, but not have the value spread correctly over the relevant months.

This is how the data is structured.

Sample

The ask is to produce a table that lays out the tasks & subtasks and straight-line averages the hours spent on a given task for the relevant months. (Total / # months -> the months between start & end dates).

For example, in this you see I'm filtering the subtasks from the total tasks, and spreading out their respective total hours for the months the task is expected to occur.

Expected Result

I'd like to focus on the bottom table for now, as the top table sums from that, I think I can adjust the rest accordingly.

Right now I take a sumifs from the total per task / month # for that task / day of the header just to get it to spill across the columns. However, this is spreading the same value across all months for the tasks, not just the relevant months. So I tried an If to check if the date is between the start and end date but then it won't spill. And if I do this alone, it doesn't properly validate the logic.

I also tried to use a LET statement to define the start date & end Date, then if and to check the month header spill range to see if that value is between those dates, and if so multiply the value by 1 or 0 to try and zero out irrelevant values.

This is currently what's happening as I try to check the columns against the xlookup results of the rows and apply it to the intersections.

What's happening

Here is a markdown table to get the basics down.

My filter formula for the tasks: =FILTER(A5:A16,MOD(A5:A16,1)<>0)

My formula for the date headers: =DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1)

Person 1 Person 2 Person 3 Person 4 Person 5
$ 100 $ 150 $ 200 $ 175 $ 95
Task Start End Months Total
1 1/1/2024 8/1/2024 8 5 15 25 12 10 67
1.1 3/1/2024 4/1/2024 2 5 5 10 20
1.2 5/1/2024 7/1/2024 3 15 12 27
1.3 5/1/2024 8/1/2024 4 20 20
2 7/1/2024 10/1/2024 4 20 20 30 - 60 130
2.1 7/1/2024 9/1/2024 3 20 20 40
2.2 9/1/2024 10/1/2024 2 30 60 90
3 6/1/2024 10/1/2024 5 20 - 45 55 30 150
3.1 6/1/2024 7/1/2024 2 10 10
3.2 9/1/2024 10/1/2024 2 10 15 25
3.3 9/1/2024 10/1/2024 2 25 25
3.4 9/1/2024 10/1/2024 2 30 30 30 90
Solution

  • With the older Excel functions
    The formula is in cell N9 and drag to right and then down.

    =IF(AND(N$9>=VLOOKUP($M10,$A$5:$J$16,2,FALSE),N$9<=VLOOKUP($M10,$A$5:$J$16,3,FALSE)),VLOOKUP($M10,$A$5:$J$16,10,FALSE)/VLOOKUP($M10,$A$5:$J$16,4,FALSE),"")

    enter image description here

    After clarifying:
    The mod SPILL formula of yours

    =LET(start,XLOOKUP($M10#,$A$5:$A$16,$B$5:$B$16),
end,XLOOKUP($M$10#,$A$5:$A$16,$C$5:$C$16),
logic, IF((start<=N$9#)*(N$9#<=end),1,0),
rates,XLOOKUP($M10#,$A$5:$A$16,$J$5:$J$16)/XLOOKUP($M10#,$A$5:$A$16,$D$5:$D$16),
logic*rates)

    The result is the above screenshot. Empty cells are filled with 0.