CountIF + Expressions

    I am trying to count the number of cells with a date within the last 7 days. CountIF supposedly supports expressions, but I cannot get it to recognize the following:
    COUNTIF(J1:J80,">(Today()-7)")


    The function will will work if I enter the date I want to see if the field is greater than =COUNTIF(J14:J31,">01/01/2003"), but I want the date to change for the current day...


    Plz advise...


    I've got a solution that works, but it involves setting a cell to Today()-7 and then entering: =COUNTIF('1.4A'!J2:J31,A9) + COUNTIF('1.4A'!J14:J31,A10) + COUNTIF('1.4A'!J14:J31,A11) + COUNTIF('1.4A'!J14:J31,A12) + COUNTIF('1.4A'!J14:J31,A13) + COUNTIF('1.4A'!J14:J31,A14+'1.4A'!G58) + COUNTIF('1.4A'!J14:J31,A15) + COUNTIF('1.4A'!J14:J31,A16)


    but I don't like this impletmentation....

    Hi, you wrote:


    "but I don't like this impletmentation...."


    Wow, can't blame you for that!!


    Try this syntax based on your original formula:


    =COUNTIF(J1:J80,">"&TODAY()-7)

    Tom Urtis

    One more thing and then I'll take a refresher course.... How can I make Excel count the cells as described in the original post AND only count them if another cell has a value.. So... COUNT J1:J80 WHERE J1:J80=Today()-7 AND F1:F80=Pending


    so only count the cells that are within the last seven days and Pending.

    Quote

    Originally posted by Anonymous
    One more thing and then I'll take a refresher course.... How can I make Excel count the cells as described in the original post AND only count them if another cell has a value.. So... COUNT J1:J80 WHERE J1:J80=Today()-7 AND F1:F80=Pending


    so only count the cells that are within the last seven days and Pending.


    for multiple criteria, you'll probably need =SUMPRODUCT (for ease) or need to take a look at Excel's Database functions (all proceeded with D.... ie DCOUNTA, DSUM etc etc) if you want some serious info without performance degredations...


    however : sumproduct :


    =SUMPRODUCT((J1:J80=TODAY()-7)*(F1:F80="Pending"))


    for an ameuter explanation of what this formula is actually doing in the background, see the earlier thread :


    http://www.ozgrid.com/forum/viewthread.php?tid=220

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login