• Count Blank Cells by running date series

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Count Blank Cells by running date series

    Author
    Topic
    #498005

    I have a spreadsheet with dates in row 1 across the top that errors occurred at a number of cash registers, with the register numbers listed down Column A. Below each date is an “X” for each register that had an error that day. I need to keep track of how many errors occurred on each register in the last 90 days, so want to count the nonblank cells in each row for the columns for only the last 90 days, based on the TODAY()-90 value. Playing with COUNTA, COUNTIF, COUNTIFS functions, but can’t figure out how to test the date in Row 1 while using one of the various COUNT functions in each register row below to count the nonblank X cells. I can see the simple way would be to replace the X’s with the date and use CountIF, but I’m curious how to do the first option with the X’s.

    Thanks
    Steve

    Viewing 0 reply threads
    Author
    Replies
    • #1483150

      Gordon,

      Try something like this where B1 to Z1 is the row range of dates and B2 to Z2 is the row for register 1 containing the X. Place the formula in cell AA2

      =COUNTIFS(B1:Z1,”>”&TODAY()-90,B2:Z2,””&””)

      The count for the last 90 days would be the number of X’s beyond 10/5/2014.

      38889-Countifs3

      This could also be done using a better method that counts only an X’s instead of non-blank cells.

      =COUNTIFS(B1:Z1,”>”&TODAY()-90,B2:Z2,”=X”)

      HTH,
      Maud

      • #1483242

        Gordon,

        Try something like this where B1 to Z1 is the row range of dates and B2 to Z2 is the row for register 1 containing the X. Place the formula in cell AA2

        =COUNTIFS(B1:Z1,”>”&TODAY()-90,B2:Z2,””&””)

        The count for the last 90 days would be the number of X’s beyond 10/5/2014.

        38889-Countifs3

        This could also be done using a better method that counts only an X’s instead of non-blank cells.

        =COUNTIFS(B1:Z1,”>”&TODAY()-90,B2:Z2,”=X”)

        HTH,
        Maud

        Thanks, either of those work. Now is see how the COUNTIFS works. Nice

    Viewing 0 reply threads
    Reply To: Count Blank Cells by running date series

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: