Google-sheets – Google Sheets query how to make MATCHING skip (not match with) blank cells

google sheetsgoogle-sheets-query

I am running a query in Google Sheets to analyze a bank account statement's transaction text (column D) against predefined categories (found in range $T14:$BG14 for this particular row).

My problem is that every once in a while the transaction text is blank, and within my range of predefined texts there will also be blanks for certain categories, which produces a false match. As you can see from the screenshot below, each category can be set to match against any number of transaction texts.

Is there a way to make this line

D matches '" & JOIN("|";$T14:$BG14) &"'

only match if the value is not blank (or null or whatever Google Sheets will identify an empty cell as)?


Here is the entiry query, and some sample data.

QUERY(
    'Kontoutdrag länsförsäkringar'!$A$2:$F$999176;
    "SELECT sum(F) WHERE 
        B >= date'" & TEXT(DATEVALUE(C$4);"yyyy-mm-dd") & "' AND
        B <= date '"& TEXT(DATEVALUE(C$5);"yyyy-mm-dd") &"' AND
        D matches '" & JOIN("|";$T14:$BG14) &"'
        label sum(F)'' ";
    )

Cells U17 and V17 in this screenshot will for example create a match for every line on my bank account statement that lacks a transaction text.

enter image description here

Best Answer

Just add this: AND D != ''

So your whole line would go:

D matches '" & JOIN("|";$T14:$BG14) &"' AND D != ''

As I was describing the problem the answer came to me, and I realize it's rather simple, but since I spent this time writing up the question I might as well post it rather than just deleting everything.