I have an input sheet with staff salaries - and starting dates etc to pull in monthly salary costs for forecasting. But also want a 'profile' to enable a user to input a revised amount (Usually upward) during the course of the next 2 years from the start of current year. These will be used to override the default amount on the calc sheet
Calc sheet has all the breakdowns of everything. But I am trying now to incorporate the profile from the Input sheet into my basic monthly salary amounts. Looking for something like this. Ignore the combining with default values as this is straightforward. But I need the array of override values as below
I thought this would be quite easy but it seems not. As the periods will change from monthly to 3 monthly, one needs to sum based on dates. I have the start and end of period at the top so can do that. But the array of periods on the calc sheet does not match that on the input sheet which will just allow for 2 years of overrides, after which, simple inflation will kick in. Can't really forecast salary increases going out more than 2 year and for new starts the default value (not shown here) will be used with a starting date. So this will only be used for changes during the immediate forecast period.
A simple sumifs does actually work for a single row but I want to create a full 2D array.
=SUMIFS(Inputs!$N$72:$AJ$72,Inputs!$N$71#,">="&L3#,Inputs!$N$71#,"<"& L4#)
Tried sumproduct also and used MAP function to map the starting ending dates of the periods
=MAP(L3#,L4#,LAMBDA(x,y,SUMPRODUCT(Inputs!$N$72:$AJ$72,(Inputs!$N$71#>=x)*(Inputs!$N$71#<y))))
L3 has the array of period start dates and L4 has the period end dates (Calc sheet). $N72:$AJ$72 holds the range of salaries for the profile and N71 has the header dates for the profile (Inputs sheet). This works fine as does a simpler Sumifs without the need to MAP. BUT. If I change the range of $N72:$AJ$72 to contain more than one row, I get a calc error. So fine on a row by row but can't get it all in a single 2D array.
I tried ByRow in conjunction but that fails. Seems I can't combine ByRow with another lambda function say from MAP or such. I also tried using a Reduce with VSTACK. One problem I see is that the array of dates on the profile is not the same as that on the Calc sheet period start & end dates and I can't see how to align these in a clever way. Its not as easy as just mapping as the profile dates will always be in months but the Calc sheet is dynamic and the current year and forecast years can be set to 1,3,6 or 12 months. I even tried creating a THUNK for one of the lambda functions. But always get the calc error Because only sumifs actually seems to work with 2 dynamic ranges of different sizes, but I can't use an input array with Sumifs (only a range - but it also deoesnt seem to work with BYROW) and Sumproduct needs me to use a MAP function (or similar) to match the date criteria in one range (actually in 2) to the criteria range of the profile, I can't see how to avoid double nested lambdas. I was surprised that the ByRow didn't work as a wrapper around the map and sumproduct (Or sumifs)
Anyhow, I know there are so many super smart people out there, it is probably not that hard but for me, I've run out of options to try. I even considered a matrix multiplication but couldn't get my head around that either!! I'm sure there is a solution using that.
PS. My attached sheet doesn't use the same refs as described above. I put it together to illustrate the issues. Just took a camera pic and laid it roughly on top of the cells so that the refs - starting ones on some do actually reflect the above.
Accepted Solutions