CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasrankingcountif
ncsushley
ncsushley

Reputation: 27

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

Upvotes: 0

Views: 1288

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

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

Upvotes: 0

Tedinoz
Tedinoz

Reputation: 8044

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

Upvotes: 1

Related Questions