How to make select with variable using Google Sheets

Question

I have a Google sheet with several columns, where are recorded support requests from clients.

     A                B                      C
-+---------------+------------------------+-------------
1| Date-1        | John                   | Ticket-101 
2| Date-1        | Anita                  | Ticket-102
3| Date-2        | John                   | Ticket-103
4| Date-3        | Dani                   | Ticket-104
5| Date-3        | Billy                  | Ticket-105

I want to create two new columns with statistical data about the clients. In these new columns, I want to have the client name and number of opened support tickets.

The end result must be:

     A                B             C            D          E
-+---------------+------------+-------------+-----------+---------------
1| Date-1        | John       | Ticket-101  | John      |   2    |
2| Date-1        | Anita      | Ticket-102  | Anita     |   1    |
3| Date-2        | John       | Ticket-103  | Dani      |   1    |
4| Date-3        | Dani       | Ticket-104  | Billy     |   1    |
5| Date-3        | Billy      | Ticket-105  |

I created the D column in this way:

=UNIQUE(QUERY(B1:B))

For counting how many times the client contact us I use:

=COUNTA(IFERROR(QUERY(B1:B, "select B where B='John'", 0)))

Of course, this is a very stupid solution, because for every new client I must to create a new formula with

....where B='Client name'".....

I'm wondering is it possible to create a formula in a way in which the name of the client is automatically populated? I imagine something like that:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(B1:B, "select B where B='value-of-D'", 0))))

score 1 · Accepted Answer · answered Oct 08 '19 at 16:12

1

=QUERY(B:C, 
 "select B,count(C) 
  where B!='' 
  group by B 
  label count(C)''", 0)

also you can order it like:

=QUERY(B:C, 
 "select B,count(C) 
  where B!='' 
  group by B 
  order by count(C) desc
  label count(C)''", 0)

answered Oct 08 '19 at 16:12

player0

124,011
12
67
124

Thank you player0 and @JPV The both solution works in the same way, but there is another problem I had no idea. The results are in alphabetical order by client names. I have additional columns F, G, H where have to write additional information manually. For example notes. That's why after every new ticket, the pairs "Client name: count" float up/down, depending on the name of the client submitted a new ticket. I want the name of clients to stay statistically, where the new names to be added at the bottom of the list. Do you have ideas on how we can solve that? – CyberUser Oct 09 '19 at 14:46
can you share a copy of your sheet? – player0 Oct 09 '19 at 15:09
Here you are the example sheet - https://docs.google.com/spreadsheets/d/1lH8cMnfU6L3vVdXk2VoSR0I1943tGIHJlQHj-_5GZTo/edit?usp=sharing – CyberUser Oct 09 '19 at 16:04
`=ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(B2:B), QUERY(B2:C, "select B,count(C) where B!='' group by B label count(C)''", 0), {1,2}, 0)))` – player0 Oct 09 '19 at 16:26

JPV · Answer 2 · 2019-10-08T15:35:34.460

0

Try

=query(B:C, "Select B, count(C) where B<>'' group by B label B 'Name', count(C) 'Count'", 1)

and see if that works?

edited Oct 08 '19 at 15:35

answered Oct 08 '19 at 14:40

JPV

26,499
4
33
48

How to make select with variable using Google Sheets

2 Answers2