Forum Discussion
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 aro...
=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.
- 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.
