It sounds like your QUERY function is tripping up on mixed data types in column P—a super common gotcha with Google Sheets' QUERY tool, which relies on auto-inferred column types from the first few rows of your data. Let's break down why this is happening and how to fix it:

Why You're Seeing Weird Results #

Google Sheets' QUERY uses the Google Visualization API under the hood, which guesses the data type of a column based on the first ~100 rows. If column P has a mix of text and numbers, or even hidden whitespace/special characters, the API might misclassify values later in the column as NULL—or worse, produce unexpected concatenated rows when it can't parse the data consistently.

Solutions to Try #

1. Force Consistent Type Checking in QUERY #

Instead of relying on P IS NOT NULL, explicitly convert P to a text (or numeric) type to ensure all non-empty values are captured. Here are two tailored options:

For Mixed Text/Numbers in P:

=QUERY(A3:X,"SELECT F,P WHERE D='G' AND TO_TEXT(P) <> '' ORDER BY P")

By converting P to text first, we avoid type mismatches—any non-empty value (whether number or text) will be recognized as valid.

For Numeric-Only P Values:

If P should only contain numbers, use TO_NUMBER with IFERROR to handle accidental text entries gracefully:

=QUERY(A3:X,"SELECT F,P WHERE D='G' AND IFERROR(TO_NUMBER(P), '') <> '' ORDER BY P")

This filters out both empty cells and text that can't be converted to numbers.

2. Switch to FILTER (More Reliable for Messy Data) #

The FILTER function doesn't rely on type inference the way QUERY does, making it more robust for irregularly filled columns. This formula replicates your original QUERY logic but with better type handling:

=SORT(FILTER({F3:F,P3:P}, D3:D="G", NOT(ISBLANK(P3:P))), 2, TRUE)

• {F3:F,P3:P} selects the exact columns you need (F and P)
• D3:D="G" filters for rows where column D is "G"
• NOT(ISBLANK(P3:P)) excludes empty cells in P
• SORT(..., 2, TRUE) sorts the results by the second column (P) in ascending order

3. Clean Up Hidden Characters in Column P #

Sometimes wonky behavior comes from invisible characters (like spaces or line breaks) that make cells look non-empty but act like NULL. Add TRIM to your QUERY to eliminate this edge case:

=QUERY(A3:X,"SELECT F,P WHERE D='G' AND TRIM(TO_TEXT(P)) <> '' ORDER BY P")

Quick Test to Confirm the Root Cause #

Add a helper column (say column Y) next to P with =TYPE(P3)—this will return 1 for numbers, 2 for text, and 4 for blank cells. If you see mixed types in rows where QUERY is failing, that confirms the type inference issue is to blame.

内容的提问来源于stack exchange，提问作者E Tarasewicz