Search code examples
if-statementgoogle-sheetsgroup-bygoogle-sheets-query

Google Sheets - Group By If/Then Buckets, Calculated Values

In Google Sheets, I'm trying to write a Query (not a stand alone Pivot Table nor script), which groups by a calculated value based on an if/then statement.

Here's my sample data:

Name    Days
Bob     0
Ed      1
Frank   2
Joey    4
Deluth  7
Henry   12

Grouping by column B is fairly straight forward: 

=QUERY(A1:B7,"SELECT B, COUNT(B) GROUP BY B")

which outputs:

Days    count
0       1
1       1
2       1
4       1
7       1
12      1

But what I'm trying to do is group the days in to specific buckets. The buckets and desired output would be this desired output:

Days    count
0       1
1       1
2-5     2
6-9     1
10+     1

So it's almost an "if 0 then '0', if 1 then '1', if >1 AND <=5 then '2-5', etc., with a Group By at the end? How would this be written in QUERY format?

Solution

  • If you had the upper limit of each range in (say) G2:G6, you could use Frequency to get the result:

    =frequency(B2:B,G2:G)

    Then you could combine this with your bin cells to get the two columns together:

    =ArrayFormula({D2:D6,frequency(B2:B,G2:G)})

    Extracting the upper limit from your bin cells is a bit awkward, but you could put it all together like this without a helper column:

    =ArrayFormula({D2:D6,frequency(B2:B,--(right(D2:D5,len(D2:D5)-iferror(find("-",D2:D5),0))))})

    enter image description here

    Note that you don't need to define the '10+' range except for display purposes, Frequency automatically groups all remaining values (above 9) into a separate bin.

    If you preferred, you could recode the original day values using a series of nested if statements or (better) vlookup and pass the result through to a query with grouping.