1

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

  • 1
    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
0

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

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.