This is the same situation I used when I posted about finding the sum which can be found here. HOWEVER, this time I need to find the COUNT.
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 COUNT 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've also tried to use the SUMPRODUCT formula and the COUNTIF formula, but I can't figure out how to manipulate the variables to produce the desired results.
Any suggestions? Please help! I'm pulling my hair out (again)!
Bookmarks