Hi
I am hoping that someone can help me with a problem I am having. My Excel skills are for the most part self taught and I sometimes find multiple functions/nesting quite hard to get my head around.
=SUMIFS(B5:H5,B3:H3,">="&@$AJ5:$AJ12,B3:H3,"<="&@$AK5:$AK12)
The above should hopefully make it fairly clear what I am attempting. I only need a working formula to place in I5 and replicate across a year's worth. I want to be able to enter start/end dates for each of the individuals named in the A1 column and for those dates to appear in the columns after "Sat" at the end of each week, and then be able to freely move individuals to any row in the sheet where necessary whilst retaining the correct holiday dates.
I don't know the best way to achieve this, if there is a way of making it possible with, for me, quite a complex sequence of SUMIFS or if I need to be using a mixture of SUM/SUMIF and INDEX/MATCH. As the end result could be quite a lot of information I would like the least memory draining option possible.
Hope someone can help
Ian
=SUM(INDEX(B5:H5,MATCH(DAY(INDEX($AJ$3:$AJ$12,MATCH(1,(A5=$AI$3:$AI$12)*($AL$3:$AL$12=1),0))),$B$3:$H$3,0)):INDEX(B5:H5,MATCH(DAY(INDEX($AK$3:$AK$12,MATCH(1,(A5=$AI$3:$AI$12)*($AL$3:$AL$12=1),0))),$B$3:$H$3,0)))You can try this formula in cell I5. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In order to identify the week i've added column AL. If the entry in this column is 1 then the week from day 1 to 7 is calculated for 2 the week from day 8 to 14 is calculated.
=SUM(INDEX(J5:P5,MATCH(DAY(INDEX($AJ$3:$AJ$12,MATCH(1,(A5=$AI$3:$AI$12)*($AL$3:$AL$12=2),0))),$J$3:$P$3,0)):INDEX(J5:P5,MATCH(DAY(INDEX($AK$3:$AK$12,MATCH(1,(A5=$AI$3:$AI$12)*($AL$3:$AL$12=2),0))),$J$3:$P$3,0)))This is the formula in cell Q5.
