I’m working on a Google Sheets project where I need to highlight rows based on what a QUERY formula returns. The issue I’m running into is that my QUERY function works fine and gives me the numbers I need (0 or something higher), but it always adds a header row that I don’t want.
Because of this extra header row, my conditional formatting gets messed up after a few rows since each query result takes up 2 rows instead of 1.
What I’m trying to do is check if names I enter match against a “do not use” list. When someone enters a name that appears on this restricted list, I want the entire row to turn red as a warning.
Here’s my current formula:
=query(B:E,"select count(B) where D contains '"&F1&"' and E contains '"&F2&"'")
In this setup, B:E contains my reference data (B has ID numbers, C has full names, D and E have first and last names). F1 and F2 are where I put the first and last names to check, but later I’ll replace these with formulas that automatically split the full name from the input.
Any suggestions on how to fix this header row problem so my conditional formatting works properly?
Yeah, this is a classic QUERY function headache. I ran into the same thing building a validation system for client data. Skip the header workaround entirely - just use COUNTIFS instead. Try =COUNTIFS(D:D,F1,E:E,F2). This counts matches in both first and last name columns without any header mess. For conditional formatting, reference this formula and trigger when it’s greater than 0. Each result takes exactly one cell, so your formatting stays aligned. Way more reliable than wrestling with QUERY results, especially with dynamic ranges.
I’ve faced this exact issue as well; the extra header row can be quite frustrating. A good workaround is to use the INDEX function to bypass the header: =INDEX(query(B:E,"select count(B) where D contains '"&F1&"' and E contains '"&F2&"'"),2,1). This way, it returns the value from row 2, column 1 directly. Alternatively, consider using the FILTER function, which can provide a cleaner solution: =COUNTA(FILTER(B:B,D:D=F1,E:E=F2)). This counts the occurrences without including the header. For your conditional formatting, refer to these adjusted formulas in your rules and set them to highlight if the result exceeds zero. This will ensure your formatting functions correctly without interference from headers.
Try adding label count(B) '' to your query - this kills the header completely. So you’d have =query(B:E,"select count(B) where D contains '"&F1&"' and E contains '"&F2&"' label count(B) ''"). The empty label tells Sheets to skip the column name. Perfect for conditional formatting since you get just the number without extra rows screwing things up.