Forum Discussion

averki's avatar
averki
Copper Contributor
Feb 01, 2023

Need help with a formula that sums values between two dates matched to a name

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

excel
Formulas and Functions

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 02, 2023

    averki 

    =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.

     

    • averki's avatar
      averki
      Copper Contributor
      Feb 02, 2023
      Thank you! It looks about right from what I understand. I let my laptop battery die so haven't had a chance to try it out yet, will let you know how I get on.

      Thanks again for your help with this, really appreciate it.
  • averki's avatar
    averki
    Copper Contributor
    Feb 01, 2023
    Sorry, meant to say the sum of those weekday values between the dates entered . . .

Resources