# How to sum up to 4 (or more) columns in a query based on checkboxes?

Basically I have 4 checkboxes and I want to sum up to 4 columns based on those, with the formula query:

It's a bit ugly what I di and I was wondering if there was a more elegant way of doing it, especially if I want to add more checkboxes or even a multiplier to those sums. Thanks in advance for your reply.

example:

``````IF(M4,
IF(M5,
IF(M6,
IF(M7,
query(A3:E10,"Select A, (SUM(B) + SUM(C) + SUM(D) + SUM(E)) where A is not null group by A label (SUM(B) + SUM(C) + SUM(D) + SUM(E)) 'Q1-4'", 1),
query(A3:E10,"Select A, (SUM(B) + SUM(C) + SUM(D)) where A is not null group by A label (SUM(B) + SUM(C) + SUM(D)) 'Q1-3'", 1)),
IF(M7,
query(A3:E10,"Select A, (SUM(B) + SUM(C) + SUM(E)) where A is not null group by A label (SUM(B) + SUM(C) + SUM(E)) 'Q1 Q2 Q4'", 1),
query(A3:E10,"Select A, (SUM(B) + SUM(C)) where A is not null group by A label (SUM(B) + SUM(C)) 'Q1 Q2'", 1))),
IF(M6,
IF(M7,
query(A3:E10,"Select A, (SUM(B) + SUM(D) + SUM(E)) where A is not null group by A label (SUM(B) + SUM(D) + SUM(E)) 'Q1 Q3 Q4'", 1),
query(A3:E10,"Select A, (SUM(B) + SUM(D)) where A is not null group by A label (SUM(B) + SUM(D)) 'Q1 Q3'", 1)),
IF(M7,
query(A3:E10,"Select A, (SUM(B) + SUM(E)) where A is not null group by A label (SUM(B) + SUM(E)) 'Q1 Q4'", 1),
query(A3:E10,"Select A, (SUM(B)) where A is not null group by A label (SUM(B)) 'Q1'", 1)))),
IF(M5,
IF(M6,
IF(M7,
query(A3:E10,"Select A, (SUM(C) + SUM(D) + SUM(E)) where A is not null group by A label (SUM(C) + SUM(D) + SUM(E)) 'Q2-4'", 1),
query(A3:E10,"Select A, (SUM(C) + SUM(D)) where A is not null group by A label (SUM(C) + SUM(D)) 'Q2 Q3'", 1)),
IF(M7,
query(A3:E10,"Select A, (SUM(C) + SUM(E)) where A is not null group by A label (SUM(C) + SUM(E)) 'Q2 Q4'", 1),
query(A3:E10,"Select A, (SUM(C)) where A is not null group by A label (SUM(C)) 'Q2'", 1))),
IF(M6,
IF(M7,
query(A3:E10,"Select A, (SUM(D) + SUM(E)) where A is not null group by A label (SUM(D) + SUM(E)) 'Q3 Q4'", 1),
query(A3:E10,"Select A, (SUM(D)) where A is not null group by A label (SUM(D)) 'Q3'", 1)),
IF(M7,
query(A3:E10,"Select A, (SUM(E)) where A is not null group by A label (SUM(E)) 'Q4'", 1)))))
``````

here is a link to a sample sheet: Link to a sample sheet

• Hey, can share some sample data ? (dummy sheet, etc;.) – pjmg May 5 at 17:02
• Sure here's a link (it is for a game and what need to be farmed) link – Banos May 5 at 18:53

here's a way to achieve this.

The problem with your formula is that your condition is outside the query.
Right now it's something like this :

``````10 Conditions -> 10 Querys -> 1 SQL statements for each query
``````

What I can propose is this :

``````1 Query -> 10 conditions -> 1 SQL statement for each conditions
``````

First, I added some valuable information inside an array with some "settings" Then i tried to construct a sql statement when only one box is `true` :

``````="SELECT "&C1&", (SUM("&FILTER(C2:C5,A2:A5)&"))
where "&C1&" is not null AND ("&FILTER(C2:C5,A2:A5)&") > 0
group by "&C1&"
label (SUM("&FILTER(C2:C5,A2:A5)&")) '"&FILTER(B2:B5,A2:A5)&"'"
``````

Then when multiples boxes are checked :

``````=ArrayFormula(
"SELECT G, ("&TEXTJOIN("+",true,ArrayFormula("SUM("&FILTER(C2:C5,A2:A5)&")"))&")
Where G is not null AND ("&TEXTJOIN(" + ",true,ArrayFormula(FILTER(C2:C5,A2:A5)))&") > 0
group by G
label ("&TEXTJOIN("+",true,ArrayFormula("SUM("&FILTER(C2:C5,A2:A5)&")"))&") 'Q"&textjoin("-",true,FILTER(arrayformula(row(A1:A4)),A2:A5))&"'"
)
``````

Now the final `query` is :

``````=query(
Blacksmithing!G3:K10,
ArrayFormula(
"SELECT G, ("&TEXTJOIN("+",true,ArrayFormula("SUM("&FILTER(C2:C5,A2:A5)&")"))&")
where G is not null AND ("&TEXTJOIN(" + ",true,ArrayFormula(FILTER(C2:C5,A2:A5)))&") > 0
group by G
label ("&TEXTJOIN("+",true,ArrayFormula("SUM("&FILTER(C2:C5,A2:A5)&")"))&") 'Q"&textjoin("-",true,FILTER(arrayformula(row(A1:A4)),A2:A5))&"'")
)
``````

Working example here

• I'm looking into it, trying to understand fully what you did. It may take me some time and I wanted to thank you before anything else: thank you very much for your answer – Banos May 6 at 21:17
• Hey, don't forget to accept this answer if it fulfills your needs :) – pjmg May 6 at 22:00
• Yeah, I wanted to tinker it with it to make sure I understood your solution. It seems it does, though I'm wondering if there's a way to avoid the need to hard write the column targeted by the checkboxes. (In your filter function) – Banos May 6 at 22:03
• You can try `=sequence()` starting at 65 like this : `=ArrayFormula(CHAR(SEQUENCE(5,1, 65)))` or use `CELL("address",A1)` then extract the column – pjmg May 6 at 23:01