I have a worksheet with dates listed in row 30, beginning in column H and extending at least through column II. The dates are in ascending order and are both past and future dates. There are 4 blank cells between each date in row 30, which can't be edited or deleted.
I need a formula that will find the column containing the MAX date that is less than TODAY() and then sum every 5th cell of row 70 starting with I70 and ending with the cell in row 70 that corresponds to the column in which the MAX date was found in the first step. The last cell to be included in the calculation range will be the MAX date column +1.
For example, if the MAX date that is less than TODAY() is located in DS30, I need to calculate the sum of I70 + N70 + S70 + X70 + so on through DT70.
I've tried =MIN & =MAX(IF(H30:II30<TODAY(),H30:II30,0)), but it only returns the first date value in the range (MIN) or the last date value in the range (MAX). I think I'm on the right track, but I can't figure out what I'm doing wrong.
Any suggestions? Please help! I'm pulling my hair out!
Also, In the future, I will be adding more dates after column II, so I need to be able to edit the formula to eventually include columns past II.
Bookmarks