SUM, QUERY, and empty cells in Google Sheets do not work correctly
You need to filter the data using query in googlesheets.
Does not sum cells with a value with empty values.
Does anyone know how to solve this problem? As an option, drive zeros into all empty cells, but this is not the solution that is expected. Is it possible to solve the problem without zeros in empty cells?
Fomula
=query(
'исходная'!A1:AK;
"select sum(I)+sum(J)+sum(K)
where (B='Виктор' AND month(C) = month(date '2010-02-01') AND year(C) = year(date '2020-02-01'))
group by B
label sum(I)+sum(J)+sum(K)''";
0
)
1 answers
I simplified your example a bit, because some parts of the query are irrelevant. My Table
There is some data. It is important to note that cells can contain not only empty values, but also text. The values themselves can probably also be text.
If you use the formula QUERY, the result will be unexpected
=QUERY(
'Данные'!A1:AK;
"select B, sum(I)+sum(J)+sum(K) group by B label sum(I)+sum(J)+sum(K)'Сумма'";
1
)
QUERY requires unambiguity in types data. Therefore, if we can cast unknown types to 0, then we can use VALUE and IFERROR to prepare the data array.
=QUERY(
ARRAYFORMULA({'Данные'!A1:H\IFERROR(VALUE('Данные'!I1:AK);0)});
"select Col2, sum(Col9)+sum(Col10)+sum(Col11) group by Col2 label sum(Col9)+sum(Col10)+sum(Col11)'Сумма'";
1
)
I hope this is what you need.