Google Sheets ArrayFormula Countifs not working correctly

0
votes

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

2
google-sheetsgoogle-sheets-formulaarray-formulasrankingcountif
I have a countifs in an array formula that isn't working correctly Would you please explain the scenario and what outcome the countifs is intended to analyse. - Tedinoz
The sheet is a log of proofreading jobs, and the formula is intended to rank/prioritize outstanding jobs by when they are due. Column J is the date a job was returned, so the formula should only rank jobs where that column is blank. Column H is the date the job is due, and Column I is the time due. - ncsushley

2 Answers

1
votes

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

0
votes

The logic and reasoning expressed by Tedinoz is correct. The proposed solutions work as well.

Still. One can follow a different approach.
The problem is that in your second COUNTIFS condition you are mixing dates and times therefore the incompatibility giving false results.

Easily corrected by slightly tweaking your formula.

You just need to replace the two mentions of column I with H+I

={"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,H:H+I:I,">"&H2:H+I2:I) ,""),""))}

This way you create a virtual timestamp taking into account both the given date as well as the time of the date (avoiding using an extra helper column along with deleting the second COUNTIFS or radically changing your formula using the RANK function).
As an extra bonus you get to keep all columns intact since you "need those columns separate for functionality in the rest of the workbook".

The virtual timestamp works because using eg. H194+I194 (where H194 is a date 03/09/2020 and I194 is a time 8:33:00 PM), Google Sheets are smart enough NOT to just add these two cells as numeric values but to concatenate them and create the timestamp 3/9/2020 20:33:00