use:

=ARRAYFORMULA(COUNTIFS(Data!B:B, "*"&D2:D21&"*", Data!B:B, "*"&E2:E21&"*"))

0 is correct because with each new criteria_range you are ANDing the results (i.e. your formula as shown is equivalent to 'give me the count where there is a TRUE present in the same position in each column').

If you want to return 5, that implies to me that you actually want 'give me the count of TRUEs across all rows', in which case you should stack the columns into one using array literals:

=countif({D8:D16;H8:H16;L8:L16;P8:P16;T8:T16},true)

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).

See Also: Count rows with not empty value

Unfortunately, this is functions as designed from Google.
^{Although I'm not sure why it's divergent from the way that Excel calculates COUNTA.}

According to the Google Spreadsheet Documentation on COUNTA:

COUNTA counts all values in a dataset, including those which appear more than once and text values (including zero-length strings and whitespace).

Meaning that the only way to "make [the cells] completely empty" is to delete the entire contents, formula and all. But fear not...

Some workarounds:

1. Hypothetically, you should be able to do this with =COUNTIF(A3:A8,"<>"&""), but Google spreadsheets doesn't support the not equal to operator in the COUNTIF function according to these forums: 1, 2, 3

2. A workaround is to create a truthy or falsy array based on the condition and use SUMPRODUCT to count the truthy values like this:

    =SUMPRODUCT((A3:A8<>"")*1)
   

3. Another option you could pursue would be to write a custom function and add it to Drive.
   It's actually pretty easy to do so. Go to Tools > Script Editor and add the following script:

    /**
     * Works like COUNTA except does not include null strings
     *
     * @param {cellRange} myArray The value or range to consider when counting.
     * @return Returns the a count of the number of values in a dataset.
     * @customfunction
     */
    function CountNotBlank(myArray) {
      var output = 0;
      for (i = 0; i < myArray.length; i++) { 
        if (myArray[i] != "") {
          output += 1
        }
      }
      return output;
    }
   

   Then use like this:

    =CountNotBlank(I31:I)