Thread: Find last date in range (with blanks) that is less than TODAY()

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.

=MAX(IF(B4:B100<TODAY(),B4:B100)) Ctrl + Shift + Enter This will return the largest date earlier than Today. If you want to include Today as a possible output, put = in there ie <=Today()

If I understand you correctly....
• There are dates in Row_30, beginning in M30 and continuing every 5th cell to the right, with blanks between the dates.
Example:
Dates (separated by blank cells) in:
M30, R30,W30...

• There are numeric values in Row_70
• For each date in Row_30 that is less than today, add each corresponding cell from Row_70 that is one column to the right of the column with the date cell
• Add the value in I70 to that total

If that's correct, this regular formula does that for columns I through EB:

=SUMPRODUCT(OFFSET(I70,,,1,COUNTIF(I30:EB30,"<"&TODAY())*5+1)*(MOD(COLUMN
(OFFSET(I70,,,1,COUNTIF(I30:EB30,"<"&TODAY())*5+1)),5)=4))

Adjust the referenced range to suit your situation.

Is that something you can work with?

Wouldn't SUMIF suffice? Try this formula

=SUMIF(H30:II30,"<"&TODAY(),I70:IJ70)

Nope! that formula is completely wrong...Oops, Clicked SAVE too soon while I was playing around.
I believe the formula you posted is exactly right.