How to sumif values (days worked) to each month, across a range of multiple weeks

[mattdale]

The top table has weeks sorted into columns, and a count of days allocated to the weeks. The below table lists out months and the formula in the bottom table extracts the days in each of the weeks. The issue arises where there is a part day allocated, as the formula ignores this- it only counts the full days. The difference column in the below table shows the variance which should be zero if the formula was perfect.

Here is the current formula:

=let(Λ,tocol(bycol({$B$1:$V$2;$B3:$V3},lambda(Σ,chooserows(sequence(days(index(Σ,2,),index(Σ,1,))+1,1,Σ,1),sequence(index(Σ,3,))))),1,1),
countif(index(eomonth(Λ,)),eomonth(A$17,)))

Any help would be really appreciated!

All the best
Matt

 

[Tetra201]

A few questions:
1) Is it a 5-day (Mon-Fri) work week?
2) Do holidays need to be taken into account?
3) Which version of Excel will you be using? -- LET, BYCOL, LAMBDA, and many other functions are not available in Excel 2011 for MacOS.

 

[mattdale]

A few questions:
1) Is it a 5-day (Mon-Fri) work week?
2) Do holidays need to be taken into account?
3) Which version of Excel will you be using? -- LET, BYCOL, LAMBDA, and many other functions are not available in Excel 2011 for MacOS.

Thanks for the reply, firstly yes it would be a 5 day work week. No holidays wouldn't need to be taken into account. The formula / sheet was actually put together in Googlesheets. If there's one which might work across both that would be amazing. It feels like a relatively simple challenge, although allocating days to weeks, then months has proved a little trickier than first thought! Any tips / guidance / opinion on whether it's possible would be really appreciated? Also looking potentially for a professional consultant for this and a few other spreadsheet challenges.

 

[Tetra201]

See if the following formula works for you:

=SUM(BYCOL($B$1:$V$2,LAMBDA(a,MAX(0,NETWORKDAYS(MAX(CHOOSEROWS(a,1),B$17),MIN(CHOOSEROWS(a,2),EOMONTH(B$17,0))))))*$B3:$V3/5)