Search code examples
arraysgoogle-sheetssumgoogle-sheets-formulanumber-formatting

Google Sheets: Sum, Query, other Formulas Treating Values (Formatted as NUMBERS) as Zeroes in Formulas - Unclear why

I have 15 Google Workbooks that import into 1 Workbook tab through an Importrange + Query combo function. The data is then queried and transformed across several other tabs, but the problem definitely begins on this tab.

Although I've manually forced a format change to "Number" in each workbook source AND in Columns CO:CQ (where the problematic data lands), all functions see the data as zero or null. Here is what happens when I set a random cell (CW33) equal to a cell in one of the trouble columns (CO33) enter image description here

However, when I wrap the cell in an =sum(), the workbook returns "0": enter image description here

I have no idea how to force the workbook to see the values in these rows as numbers without creating an entirely new column - does anyone have any ideas on how to fix it while preserving the column structure?

Thanks!

Solution

  • try:

    =SUM(CO33*1)

    for multiple cells it would be:

    =ARRAYFORMULA(SUM(CO33:CO34*1))

    or:

    =SUMPRODUCT(CO33:CO34)