Sequence a 'sumif' with exclusions in Excel

Question

I have sheets that autocalculate the P&L for each project. At the moment they're using a fixed salary, but I'd like to change it to incorporate any future salary changes. The project duration is Sequenced from L1 based on a duration parameter in E2 using =EDATE($C$2, SEQUENCE(1, $E$2, 0))

Each cell in column L sequences through the duration, calculating the relevant values.

The formula for the salary costs refences the dataset below which is also automatically generated from data in another sheet using =FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Code Name]=B2), {1,0,0,1,1,1,0,0,0,1,0,0,0,1,0,0,0,0}) and can have a variable number of entries:

The current formua for calculating the salary costs in L7 (from the first image) that someone on here kindly helped me with is =MMULT(SEQUENCE(1,ROWS($A$18#),1,0),($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))*OFFSET($A$18#,0,5,,1)) and uses the column F (which I'm hoping to remove) from the second image .

I've got a new set of data in the table EmployeeSalaryTbl for the salaries here (I had to add in the made up end date of 31/12/9999 for now to ensure calculations work below. Ideally, this would be blank, and I'd check for that in the below calculation):

I've begun modifying the MMult function in L7 to do the following:

1. Sequence for the duration of the project
2. Check if the employees from A18 onwards are on the project for the given month starting from L1
3. If they are then find the salary that falls within the date range from the EmployeeSalaryTbl and add them all together for the month.

This is what I have so far but unfortunately it's giving me an error:

=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),IF(AND(($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))),SUMIFS(EmployeeSalaryTbl[Salary Monthly], EmployeeSalaryTbl[Employee],$A$18#, EmployeeSalaryTbl[Salary Start Date],"<="&$L$1#, EmployeeSalaryTbl[Salary End Date],">="&$L$1#),0))

The data for the Project defined by image 2 is:

Employee	Role	Discipline	Start Date	End Date	Salary Monthly
Bob	Senior Programmer	Programming	12/01/2020	06/05/2020	£4,333
Dave	Mid Level Programmer	Programming	01/02/2020	30/05/2020	£3,167
Peter	Senior Programmer	Programming	01/01/2020	31/01/2020	£4,583
Jack	Junior Programmer	Programming	01/02/2020	30/06/2020	£2,083
Richard	Senior Artist	Art	01/03/2020	30/04/2020	£3,750
Rodney	Lead QA	QA	01/03/2020	30/06/2020	£4,333
Proj 1 - Hire 1	Senior Producer	Production	01/02/2020	30/05/2020	£3,458
Roger	QA	QA	01/01/2020	30/04/2020	£1,667
Wesley	Mid Level Programmer	Programming	01/02/2020	31/05/2020	£3,750
Rachel	Senior Artist	Art	01/01/2020	30/06/2020	£3,333
Proj 1 - Hire 2	Lead Programmer	Programming	01/01/2020	31/07/2020	£4,417

And the data in for the EmployeeSalaryTbl is:

Employee	Salary Start Date	Salary End Date	Salary	Salary Monthly	Salary Daily
Bob	01/01/2020	31/03/2021	£52,000	£4,333	£199
Bob	01/04/2021	31/03/2022	£55,000	£4,583	£211
Bob	01/04/2022	31/12/9999	£58,000	£4,833	£222
Dave	01/01/2020	31/03/2021	£38,000	£3,167	£146
Dave	01/04/2021	31/12/9999	£42,000	£3,500	£161
Wesley	01/01/2020	31/12/9999	£45,000	£3,750	£173
Jack	01/01/2020	31/12/9999	£25,000	£2,083	£96
Richard	01/01/2020	31/12/9999	£45,000	£3,750	£173
Rodney	01/01/2020	31/12/9999	£52,000	£4,333	£199
Proj 1 - Hire 1	01/01/2020	31/12/9999	£41,500	£3,458	£159
Roger	01/01/2020	31/12/9999	£20,000	£1,667	£77
Steve	01/01/2020	31/12/9999	£27,000	£2,250	£104
Rachel	01/01/2020	31/12/9999	£40,000	£3,333	£153
Peter	01/01/2020	31/12/9999	£34,000	£2,833	£130
Sarah	01/01/2020	31/12/9999	£22,000	£1,833	£84
Chloe	01/01/2020	31/12/9999	£33,000	£2,750	£127
Matthew	01/01/2020	31/03/2021	£23,000	£1,917	£88
Matthew	01/04/2021	31/12/9999	£28,000	£2,333	£107
Proj 1 - Hire 2	01/01/2020	31/12/9999	£36,000	£3,000	£138

Answer 1

With your input data, and assuming no Excel version constraints (not specified in the question), in cell H2 put the following formula:

=LET(namePrj, TB_Prj[Employee], startPrj, TB_Prj[Start Date], endPrj,
 TB_Prj[End Date],name, TB_Roster[Employee],start, TB_Roster[Salary Start Date],
 end, TB_Roster[Salary End Date],salary, TB_Roster[Salary Monthly],
 SOMs, H1:S1, EOMs, EOMONTH(SOM,0),
 BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),
 namesActive, FILTER(namePrj, (startPrj <= SOM) * (endPrj >= EOM)),
  cost, FILTER(salary, (start <= SOM) * (IF(end > 0, end, EOM) >= EOM) *
    (ISNUMBER(XMATCH(name,namesActive))),0), sum(cost)
  )))
 )

Two Excel Tables were defined to have dynamic ranges: TB_Prj for project information and TB_Roster for salary information. The month information is generated horizontally as follows in H1:

=EDATE(DATE(2020,1,1), SEQUENCE(1, 12, 0))

It generates the first day of the month. Represented in the formula as SOMs (Start Of the Month). Similarly, I use EOMs to represent each corresponding End Of the Month.

The output generates a 1x12 array as result.

Here is the output:

Showing only partially the salary table (TB_Roster)

Notes:

Please check the result, your data needs to be cleaned, for example, Peter in Salary Table has two entries overlapping the date interval.

If you cannot use tables (for example the input is based on SPILL formula like FILTER), then ranges can be used instead. You can define the following two data set ranges: prjSet, rosterSet, then define each corresponding column like this: INDEX(rng,,x), where x represents the corresponding column number on each range where rng is prjSet or rosterSet. For example name variable could be defined like this:

namePrj, INDEX(prjSet,,1)

Explanation

Using Excel Tables to have dynamic ranges, so the formula doesn't need to change when additional information is added. We use LET function to make the formula easier to maintain, defining the required inputs and intermediate results.

BYCOL is used to iterate over all SOMs values. For each start of the month (SOM), we do the following:

Find the namesActive from active projects checking that the start of the project is before or equal to SOM and the end of the project is greater or equal to EOM. In case no names from the project table satisfy the condition, FILTER returns an error (#CALC!) because empty arrays are not permitted in Excel. If this situation wants to be treated the formula needs to be adjusted (using a third input argument of FILTER and IF condition for computing cost) or better to clean the data. I would not expect for a given month not having any active project at a company level.

Once we have the list of names, we go to the second Excel Table (TB_Roster) to find a valid salary based on start, end dates.

We treat empty values in end, assuming that if there is no value (data is formatted as date, so empty means 0). The following IF condition ensures it (in the second FILTER call):

(IF(end > 0, end, EOM) >= EOM)

so if end array is positive (dates are represented as positive integers starting from 0, i.e. whole numbers), then a value was assigned, so we use this value, otherwise (0) it is replaced by EOM. When there is no end date the condition it is always true (all end values are considered). IF works with arrays, it evaluates the condition on each element of the array and returns the corresponding value on each element. By doing this we fixed the case of empty value to a valid situation, so there is no need to use a fictitious date.

The condition related to XMATCH, ensures we filter name by only the names on the previous FILTER call (valid names from the project table, i.e. namesActive). The result of this filter (cost) satisfies all the conditions and returns all matching monthly salaries.

In case there is no match based on the filter conditions, we return 0 (but another value can be used, for example NA()). In order to do that we use the third input argument of FILTER function.

Finally, we sum all the salaries that match the condition.

Bonus: Checking Interval Consistency

Based on the answer to the question: Excel: #CALC! error (Nested Array) when using MAP functions for counting interval overlaps provided by me. It can be adapted to check if date intervals are incorrect (end date before start date) or if intervals overlap for Salary Table:

=LET(names, TB_Roster[Employee], namesUx, UNIQUE(names),
 dates, TB_Roster[Salary Start Date]:TB_Roster[Salary End Date],
 maxDate, DATE(3000,1,1),
  byRowResult, BYROW(namesUx, LAMBDA(name,
    LET(set, FILTER(dates, names=name),
      startDates, INDEX(set,,1), endDates, INDEX(set,,2),
      calc, MAP(startDates, endDates, LAMBDA(start,end,
        LET(endAdj, IF(end > 0, end, maxDate),
        endDatesAdj, IF(endDates > 0, endDates, maxDate),
        startAfterEnd, IF(start <= endAdj, 0, 1),
        overlaps, IF(startAfterEnd > 0, 0,
          SUMPRODUCT((startDates <= endAdj) * (endDatesAdj >= start))-1),
        startAfterEnd &","& overlaps))),
        totals, TEXTSPLIT(TEXTJOIN(";",,calc),",",";"), intervals,
          1*INDEX(totals,,1), laps, 1*INDEX(totals,,2),
        SUM(intervals) + SUM(laps)/2
      )
    )), FILTER(HSTACK(namesUx, byRowResult), byRowResult<>0,
          "NO Overlaps or Wrong Ranges defined")
)

and the output will be for your input data: