+ Reply to Thread
Results 1 to 9 of 9

Find last date in range (w/blanks) less than TODAY() & COUNT

  1. #1
    Registered User
    Join Date
    11-22-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Find last date in range (w/blanks) less than TODAY() & COUNT

    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)!
    Last edited by cbsalt; 03-16-2011 at 12:04 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    Countif is the counterpart to SUMIF for counting...

    so try:

    =COUNTIF(H30:II30,"<"&TODAY())
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-22-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Red face Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    Oops! I just realized I was not clear at all when describing what I need to have counted.

    NBVC - Your suggestion would work perfectly under the circumstances I described in my first post!

    However, I did not include the fact that there are some blank cells in row 70, which would make the formula you suggested produce inaccurate results. Using the same example as in my first post, if the MAX date that is less than TODAY() is located in DS30, I need to calculate the number of non-blank cells from I70 + N70 + S70 + X70 + so on through DT70. I'm not sure if it makes a difference, but unlike row 30, there are entries in most (but not all) of the cells between each 5th cell in row 70. So a formula that will just count every non-blank in row 70 up to the latest date in row 30 will not work.

    Thanks again for your help!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    How about?

    =SUMPRODUCT(--(H30:II30<TODAY(),--(I70:IJ70<>""))

  5. #5
    Registered User
    Join Date
    11-22-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    I think that formula's on the right track, but it still only gives me the total number of non-blank cells between I70 and the column corresponding to today's date. I can't figure out how to modify it to only count every 5th (non-blank) cell in I70:IJ70. Any more suggestions? Thanks so much for your help!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    Maybe:

    Please Login or Register  to view this content.
    If not, then please post sample workbook showing expected result from sample data.

  7. #7
    Registered User
    Join Date
    11-22-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    13

    Talking Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    That works! Thanks so much! All I had to do was change the "" to 0 because it was counting cells that were blank. My final formula is

    =SUMPRODUCT(--(H30:II30<TODAY()),--(I70:IJ70<>0),--(MOD(COLUMN(I70:IJ70)-COLUMN(I70),5)=0))

    I also found out that the following formula also accomplishes the same thing.

    =SUMPRODUCT(N(H30:II30<TODAY()),N(I70:IJ70<>0),N(MOD(COLUMN(I70:IJ70)-COLUMN(I70),5)=0))

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    Great, you are welcome.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find last date in range (w/blanks) less than TODAY() & COUNT

    Great, you are welcome.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1