Google-sheets – COUNTIFS returns incorrect result when applied to the output of QUERY

google sheetsgoogle-sheets-query

In my spreadsheet the chart for Negative Feedback weekly pulls data from the Query sheet, but the logic doesn't work to separates negative feedback from positive feedback. The logic works fine on the non-queried tab Responses. Specifically, the command:

=Countifs(Responses!$B$2:$B$500, "221*", Responses!$E$2:$E$500, "<>"&"")

correctly returns 3, but:

=Countifs(Query!$B$2:$B$500, "221*", Query!$E$2:$E$500, "<>"&"")

returns 5 even though the data is the same. Query sheet is the result of applying QUERY to Responses.

There are five rows where B matches wildcard pattern 221*, but for two of them the column E is empty. The second criterion (nonempty cell in E) does not appear to work when applied to this output of QUERY.

Best Answer

You are hitting some strange behavior of Sheets, quite possibly a bug. I will first describe a way to reproduce it, and then a way to avoid it.

To reproduce:

Put some text in cells A1 and A3, leaving A2 blank. In cell B1, enter =query(A1:A3, "select *"). The content of cells B1:B3 will now appear to be identical to A1:A3.

But B2 is not really blank. Specifically:

=isblank(B2) returns FALSE, while =isblank(A2) is TRUE
=istext(B2) returns TRUE, while =istext(A2) is FALSE
=counta(B1:B3) returns 3, while =counta(A1:A3) is 2.
=countif(B1:B3, "<>") returns 3, while =countif(A1:A3, "<>") is 2.

To avoid

Don't use countif or counta on the results of query (at least until this bug is fixed). As an alternative, use filter command to apply the criteria, and then counta to count the (nonempty) results. Thus, instead of

=Countifs(Query!$B$2:$B$500, "221*", Query!$E$2:$E$500, "<>")

use

=counta(filter(Query!$B$2:$B$500, regexmatch(Query!$B$2:$B$500, "^221")*len(Query!$E$2:$E$500)))

which is admittedly more complex but gets correct results. There are two filter criteria, which are imposed as logical AND by multiplication:

column B entry matches the regular expression ^221 (equivalent to wildcard pattern 221*)
column E entry has positive length -- this is a robust check that is not affected by the strange behavior of query.

Since in the filtered results, the column B is guaranteed to be nonempty, counta will return the number of all rows that match the filter.

An alternative, slightly shorter formula with the same result:

=sum(arrayformula(regexmatch(Query!$B$2:$B$500, "^221")*(len(Query!$E$2:$E$500)>0)))

This evaluates the two criteria as True/False, coerces them to 0-1 integers by multiplication, and sums the results.

Formula

=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)

=ARRAYFORMULA(                           // ARRAYFORMULA start
   VLOOKUP(                              // VLOOKUP start
     B2:B,                               // search_criterion                 
     DATA!A2:G,                          // array
                                         // index start
     IF(                                 // IF start
       C2:C="1st",                       // test 
         {4,5},                          // then_value
         {6,7}                           // otherwise_value
     ) *                                 // IF end
     SIGN(                               // SIGN start
       ROW(                              // ROW start
         A2:A                            // reference
       )                                 // ROW end
     ),                                  // SIGN end
                                         // index end
     0                                   // sort_order
   )                                     // VLOOKUP end
 )                                       // ARRAYFORMULA end

// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))

Explained

The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.

Screenshot

enter image description here

Example

I've copied your file and added my solution to it: teacher references

Google-sheets – Lookup formula returns an incorrect answer

The docs say the list has to be "sorted"

Yes, this is the problem. lookup expects the range to be searched to contain a list that is sorted alphabetically (if it contains text strings) or by value (if it contains numbers). If this expectation fails, it will not tell you anything but will likely return a wrong result.

So you have two options.

(bad) Sort the search range alphabetically

Of course, you'll also need to rearrange the result range in the same way. The resulting formula is hard to read and maintain.

=lookup(I1,
       {"Fifth", "First", "Fourth", "Second", "Sixth", "Third"},
       {((D6-E6)*H6), ((D6-E6)*H6), ((D6-E6)*H6), (0.43*H6), ((D6-0.25-E6)*H6)})

(good) use hlookup instead, as the documentation itself suggests

This requires a little adjustment: only one array is given, but it has two rows: first for search, the second for results. The last two arguments indicate that the result is to be taken from the second row, and that the search range is not sorted (false).

=hlookup(I1,
       {"First", "Second", "Third", "Forth", "Fifth", "Sixth"; 
       (D6-E6)*H6, 0.43*H6, (D6-0.25-E6)*H6, (D6-E6)*H6, (D6-E6)*H6, L6*H6}, 
       2, false)

(You also had a bunch of unnecessary parentheses there)