Google-sheets – How to sum up to 4 (or more) columns in a query based on checkboxes

google sheetsgoogle-sheets-query

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

Best Answer

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"

enter image description here

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)&"'"

Output is this enter preformatted text here

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

Related Topic