ROUNDUP SUM values in a column in Google Sheets' query

1
votes

I want to ROUNDUP the sum values in a column in my google sheets QUERY. I discovered adding format sum(Col2) '#' will do the trick as long as the sum is >.5. So how do I accomplish the same result if the value is <.5?

Here is my formula:

=QUERY(QUERY(Data!A3:D, "SELECT A,sum(B),C,D where A is not null GROUP BY A,C,D format sum(B) '#'", 0),"OFFSET 1",0)

Currently, if the result is <.5, it will just display a blank space.

Here is a sample sheet

1
google-sheetsroundinggoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

1
votes

the only possible way would be:

=ARRAYFORMULA(IFERROR(ROUNDUP(
 QUERY(QUERY(Data!A3:D, 
 "SELECT A,sum(B),C,D where A is not null GROUP BY A,C,D", 0),"OFFSET 1",0)), 
 QUERY(QUERY(Data!A3:D, 
 "SELECT A,sum(B),C,D where A is not null GROUP BY A,C,D", 0),"OFFSET 1",0)))

0