I have a perplexing issue with sumif()
in a Google Spreadsheet. It is correctly summing some, but not all, of the values that match the required critera. If however I take the value that is failing to be summed, and divide it by 1, then the sumif functions correctly.
Also I have found that using round()
around the "offending" data also causes the sumif()
to function correctly. So it seems that this particular value just requires some extra manipulation, but I don't know why. The value is simply a cell reference to another cell containing a numeric value.
Why on earth would this be happening and how can I get sumif to behave consistently without having to mathematically manipulate certain cells?
Best Answer
So as @pnuts pointed out, this was merely an issue of formatting. Most of the data I was pulling from another sheet was in fact formatted numerically, but just a few cells were for some reason formatted as text, and therefore the
sumif()
was failing to include them unless mathematically altered, forcing them to a text type.The solution was simply to go into my main data sheet, and format all cells as numeric in the format I needed.