VBA Code that Sums on a Loop Based on Criteria and Linked to Calender

Hi guys,

What I'm trying to do is too complex for a regular Excel formula, so I need a VBA. Unfortunately, I know very little about VBA, so I'm really counting on this forum. I think this might be a challenging VBA, but hopefully fun too!

It's easiest to begin if you look at the attached workbook (My apologies that the formulas are hard to read, they refer to another workbook). As it is now, column CH has a formula that takes the sum of the top three and bottom three values in columns BV:CG. The criteria for "top" and "bottom" is based on the ranks in AX:BH--i.e. if a cell's corresponding "rank" is in the top three highest or lowest in all of the ranks of that row, then that cell is added to the sums in CH. *The formula also only runs if the sums of all corresponding ranks on that row add up to 45* Formula=(IF(SUM($AX4:$BH4)>=45,SUM(SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4))+SUM(SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)),FALSE))

As it is now, it's wrong.
First: Instead of using different ranks each day, I need to use the same criteria for the whole work week (5days). In other words, I need to target these parts of the above formula "SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and "SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and change them so that the large{1,2,3} and small {1,2,3} of $AX4:$BH4 hold for BV4:CF8, i.e. the whole week.
Second: The criteria on the last day of the proceeding loop is the criteria for the first day of the next loop. In other words, thought about in a real life scenario, one can only make a decision for that week based on the criteria of the day before.
Third: If the loop hits a "FALSE" statement (which would happen if the sum of the ranks is less than 45), I need excel to take the next available criteria, and apply that to the next 5 days.
Fourth: This (above statement) means that the loop needs to be linked to a calender. I can't just apply the same criteria to 5 cells, the next criteria to the next 5 cells, ect, because on the FALSE days, there would not be a sum, but instead the sum would pick back up at the next available criteria, and THEN apply that to the next 5 cells.


Even a partial code or input would be helpful. I'm really running on nothing.