Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasrankingcountif

Google Sheets ArrayFormula Countifs not working correctly

Screenshot

I have a countifs in an array formula that isn't working correctly for the data in one row, and I can't for the life of me figure out why. Here's what the data looks like:

A   H           I           J       K
1   03/09/2020  08:33 PM        
1   03/11/2020  08:16 PM        
3   03/12/2020  08:00 AM        
4   03/12/2020  09:00 AM        
5   03/12/2020  11:00 AM

This is the formula I have:

={"Priority"; ARRAYFORMULA(if(isblank(J2:J),if(isblank(H2:H)=false,
countifs(J:J,"",H:H,"<="&H2:H)
-countifs(J:J,"",H:H,H2:H,I:I,">"&I2:I),""),""))}

That second row should have a value of 2 in column A, as far as I can tell. When I change the time in column I to 08:17 PM, it works correctly. Am I missing something?

Here is a copy of my workbook: https://docs.google.com/spreadsheets/d/10pv5KvQYloxmx_7wQxlySTQslCQBFTcWwAsvkLHop7Y/edit?usp=sharing

Solution

  • You have separate Columns for "Date Due" and "Time Due" and you are creating a ranking (using countifs) by adjusting for date and time. You have found that the ranking is producing unexpected errors and don't know why.

    Your formula is" ={"Priority"; ARRAYFORMULA(if(isblank(J2:J),if(isblank(H2:H)=false, countifs(J:J,"",H:H,"<="&H2:H) -countifs(J:J,"",H:H,H2:H,I:I,">"&I2:I),""),""))}

    The error arises as a result of adjusting for Time independent of the Date. Date and Time are both Date Objects. In a Google Spreadsheet function, both are calculated as a decimal value relative to the epoch (12/30/1899 0:00:00), though formatting can is often used to mask this.

    In your formula you calculate an adjustment between the count of Dates (Column H) less the count of Times (Column I). However, this assumes that both values exist in the same sequence - this is not the case. These Times are unrelated to their Date counterparts - they exist in isolation and thus the calculation is affected by their relative value (rather than an absolute value).

    The solution to the problem is twofold:
    - record the Due Date as a Date/Time, and

    • use COUNIFS based on the Date/Time field only (delete the element -countifs(J:J,"",H:H,H2:H,I:I,">"&I2:I))

      OR

    • use the RANK function (sorting descending).

    This table shows the effect on the decimal value of the Time when taken in isolation compared to when taken as a Date/Time value).

    Date time comparisons