Sum column by the last X number of workdays

[mvparker79]

I need a formula that will sum the previous 4 workdays. Right now my formula gets messed up when it is summing over the weekend. I have no activity over the weekend so my 4 day avg is too low. But if I include the data from wednesday, thursday, friday, and today....well then my data is accurate.

[Pete_UK]

You can use this formula in I6:

=SUMIFS(B:B,A:A,">="&I4-IF(WEEKDAY(I4,2)<4,5,3),A:A,"<="&I4)

Hope this helps.

Pete

[Crooza]

This seems to work

Please Login or Register  to view this content.

change the range to suit your needs

edit - My formula calculated the average. I note you want the sum so I've amended it.

[Pete_UK]

Here's another one:

=SUM(INDEX(B:B,MATCH(I4,A:A,0)-3):INDEX(B:B,MATCH(I4,A:A,0)))

Hope this helps.

Pete

[Crooza]

Just a note. While ever your data is in the format that it is in now (ie missing weekends) all these will work. I wrote mine with the thought a weekend date might creep into the data and you would still want to exclude it so it ignores weekend dates even if they do get included in the data set. Belts and braces maybe!

[mvparker79]

Thank you so much to everyone that has posted their response. These replies were exactly what I needed! Thank you!

[Pete_UK]

Glad to hear it. You could show your appreciation by clicking on the "star" icon below any post that you have found to be helpful.

Pete