Google Sheets select, count, limit

Question

In a Google Sheets document, I've got a column which contains multiple values. I want to get the first 20 values by count, and then group the count of the others.

I've got this working code for now:

=QUERY(B2:B165,"select B, count(B) group by B order by count(B) desc limit 20 label B 'Pubblication venue'")

This will get me how many times a value appears in the column, and limit the results to 20. Now I need the count of the other results (which are ~100).

Let me explain by example. With my code I'm able to get the count of A, B and C. Now I want the count of others.

+-------+----+
| A     | 5  |
+-------+----+
| B     | 2  |
+-------+----+
| C     | 4  |
+-------+----+
| Other | 90 |
+-------+----+

you want to count other counts or others itself? – player0 Jul 01 '19 at 10:49 — player0, Jul 01 '19 at 10:49
I want to count every element excluded by the limit clause – Weizen Jul 01 '19 at 10:54 — Weizen, Jul 01 '19 at 10:54

player0 · Accepted Answer · 2019-07-01T11:13:45.383

={QUERY(B2:B165, 
 "select B, count(B) 
  where B is not null
  group by B 
  order by count(B) desc 
  limit 20 
  label B 'Pubblication venue'", 0); 
 {"Other", COUNTA(IFERROR(QUERY(QUERY(B2:B165, 
 "select B, count(B) 
  where B is not null
  group by B 
  order by count(B) desc 
  offset 20
  label count(B)''", 0), 
  "select Col1", 0)))}}

_____________________________________________________________

={QUERY(B2:B165, 
 "select B, count(B) 
  where B is not null
  group by B 
  order by count(B) desc 
  limit 20 
  label B 'Pubblication venue'", 0); 
 {"Other", SUM(IFERROR(QUERY(QUERY(B2:B165, 
 "select B, count(B) 
  where B is not null
  group by B 
  order by count(B) desc 
  offset 20
  label count(B)''", 0), 
  "select Col2", 0)))}}

This is exactly what I needed, and it works like a charm. Thank you. — Weizen, Jul 01 '19 at 19:43

Google Sheets select, count, limit

1 Answers1

_____________________________________________________________