Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Santa Barbara, California, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Blank Cells by running date series

    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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    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.

    Countifs3.png

    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
    Last edited by Maudibe; 2015-01-03 at 17:54. Reason: upload image

  3. #3
    Lounger
    Join Date
    Dec 2001
    Location
    Santa Barbara, California, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Quote Originally Posted by Maudibe View Post
    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.

    Countifs3.png

    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

Posting Permissions

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