How can I adjust my Google Sheets QUERY formula to manage various data types correctly?

Other Questions Google Sheets
1

I’m dealing with a Google Sheets formula that employs QUERY alongside LAMBDA and INDEX functions. The main issue is that numeric values processed by my query stop functioning correctly for calculations. I constructed this formula by merging different functions to extract data from my primary sheet.

Here’s my current formula:

=FILTER(MAP(x, y, {y; IFERROR(VLOOKUP(y, x, ROW(INDIRECT("1:" & ROWS(QUERY(x, "limit 1", )))), ))}) (QUERY(DATA!A1:K, "select A, sum(G), H where C = 'abc123-def4-5678-ghij-klmnopqrstuvw' and I = 'Main' group by A, H pivot D", 1), {"Record Id", IFERROR(LOOKUP(ROW(INDIRECT("1:9")), {CONFIG!C2:C, CONFIG!F2:F }, 2, )), "Document Id"}))

The issue I’m facing:

When I try to copy numeric values from the query results into other cells for mathematical operations, they appear as 0 instead of showing the correct calculated numbers. Text values copy without a problem, and boolean and date values can be processed through functions. However, straightforward copying of numbers fails.

Here’s what works:

  • String values copy successfully.
  • Boolean values function with IF statements.
  • Date values work adequately with date functions.
  • When I use a formula reference (such as multiplying by 2), the numbers function correctly.

Here’s what doesn’t work:

  • Directly copying number values for addition.
  • Basic sum formulas that utilize the query results fail to compute correctly.

I believe this issue arises because the QUERY function determines the data type based on the most common format found in the source data. Since my data contains a mix of formats (numbers, text, dates, booleans) within the same column structure, it complicates things.

Is there a way to adjust my query formula to ensure that numbers remain as numbers even after being copied? The solution should still function appropriately even if other data formats are more prevalent than numbers in my original data.

2

It’s a Google Sheets type inference issue. When QUERY hits mixed data types, your complex formula loses the type conversion somewhere along the way. Don’t bother with VALUE() or FORMAT() - just add explicit column headers with type hints in your QUERY string. Try ‘select A, sum(G) format sum(G) “#”’ to force numeric formatting. I’ve also had luck using ARRAYFORMULA with TO_TEXT(), then VALUE() on specific columns after QUERY runs. Here’s what’s happening: QUERY detects types early, but your nested functions convert everything back to text afterward. Break your formula into stages instead. Run QUERY first, then apply LAMBDA and MAP separately. You’ll control data types at each step instead of letting them get scrambled through the whole pipeline.

3

classic sheets data type problem. try wrapping your numeric columns with VALUE() in the query. it’l help convert them to proper numbers. also, check your source data for any hidden characters messin with the format.

4

Had the same headache with mixed data types in QUERY results. Google Sheets treats the whole column as text when it hits mixed formats during queries. Skip VALUE() and use explicit type casting in your QUERY statement instead. Try FORMAT() functions around numeric columns - something like FORMAT(sum(G), “0”) forces the output to stay numeric. What worked for me was separating numeric aggregations into their own QUERY statements first, then combining results with ARRAYFORMULA. Stops the type confusion when everything processes together. Also check that your source column G doesn’t have text entries mixed in - even one text value makes the entire result column act like text.