Google-sheets – Count query based on multiple conditions

google sheetsgoogle-sheets-querypivot table

I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.

For reference, when our guild goes through dungeons, we fairly distribute items that drop based on a 'plus count' system. That is, everyone starts at 0 and can roll dice against other 0s to try to win an item. If someone wins something, they become +1 and can then only roll against other +1s, so one person doesn't win everything just because they're luckier.

So, we have a sheet that logs every single item that drops, who won it, where it was won and if it counts to the +1 system (there are some items that are not worth counting for). Next to the item log, there's a query that displays what everyones plus count is at automatically, which looks like this:

Loot Tracker
--------------
Name     Count
--------------
Jim      2
Bob      1
Liam     1
Dave     1
Luke     1

The actual query for that is this, currently:

 =QUERY(A:I,"
    Select D, count(E) 
    where C = 'Victrix' and G = true and A >= date '"&TEXT(IF(WEEKDAY(J1,1)=3,J1,J1-WEEKDAY(J1+4)),"yyyy-mm-dd")&"' and A <= date '"&TEXT(J1+8-WEEKDAY(J1+5),"yyyy-mm-dd")&"' 
    group by D 
    order by count(E) desc, D 
    label D 'Name', count(E) 'Count'
 ")

We're now at a point where people aren't just rolling for their own character, but for their other specializations (something they won't use right away, but no-one else needs it for their main character, so they're rolling for what's called an 'off-spec') and this also needs to be tracked on the loot tracker.

Say I have this data set:

 |  A         B              C      D
----------------------------------------------
1|  Name      Item           +1     Spec
----------------------------------------------
2|  Jim       Lorem          [✓]    MS
3|  Bob       Ipsum          [✓]    MS
4|  Jim       Dolor          [✓]    OS
5|  Liam      Sit            [✓]    MS
6|  Dave      Amet           [✓]    MS
7|  Luke      Consectetur    [✓]    OS

I somehow need a query, or single formula, that makes the loot tracker work like this:

Loot Tracker
-------------------
Name     MS+   OS+
-------------------
Jim      1     1
Bob      1     0
Liam     1     0
Dave     1     0
Luke     0     1

I hope this makes some sense. I have no idea how I'd go about this. I tried separate query formulas on the third column here. However it didn't sort correctly or line up with the names.

It needs to be ordered by MS first, and then OS.

Best Answer

The following code will give you the exact output you are looking for in your bottom example:

=arrayformula(if(
         query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc")="",0,
         query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc label Col1 'Name', Col2 'MS+', Col3 'OS+'")))

How the formula works:
1. We multiply column C by 1 to convert the checkboxes to numbers and use the pivot feature in the query to break out the columns the way you want.
2. You then have to wrap the entire thing around another query to sort it the way you want because you can't sort and pivot in the same query with aggregated columns.
3. Then sadly, we wrap the entire thing in an if statement so if there are any blanks we can turn them into zeros.
4. If you didn't care about the blanks the formula would be half as large and you would basically just use the false portion of the if statement.

Related Topic