Convert alphabetical Google Sheet column names to numbers

Google Sheets QUERY statements typically work with A, B, and C column names just fine.

=QUERY(A1:B326,"SELECT SUM(A) WHERE B = 'Active'")

However, QUERY requires column names Col1, Col2, Col3 instead of A, B, and C when querying data from a different Sheet.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1KYK-Op3XKNElfwD0cz0SmBVA1y8NgnCr4bU2u6NJuN0/edit#gid=0", "Sheet2!A1:B326"),"SELECT SUM(Col1) WHERE Col2 = 'Active'")

I find it difficult to remember the letter O is the 15th letter of the English alphabet. There is no chance that I am going to memorize that column BH is the 60th.

Column name converter

Converts A to 1 and BH to 60



What’s going on in that second query?

QUERY can pull data from a Google Sheet at a different URL if a call to IMPORTRANGE is passed into the first parameter of QUERY.

Referencing tabs with spaces in their names

Use single quotes around a tab name that contains spaces, like ‘Raw Data’.

=QUERY('Raw Data'!A2:E700,"SELECT D,E,B WHERE A = 'received'",1)