CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformulaspreadsheet
Cattivellio
Cattivellio

Reputation: 15

Count values without having to specify each

I need to count how many times ids repeats, without having to specify each id. In my case I need it for know how many customers come 3 times or more in a month. Here is an example of where Im getting the data from:

customers|   id
------------------
person 1 | 2433340
person 2 | 3457548
person 3 | 3457584
person 4 | 4343218
person 4 | 4343218
person 4 | 4343218
person 3 | 3457584

And this one is the one that I need to fill:

Times that customers come
--------------------------
1 time  | 2
2 times | 1
3 times | 1

Upvotes: 0

Views: 49

Answers (2)

JvdV
JvdV

Reputation: 75990

I have used:

enter image description here

Formula in D2:

=QUERY(QUERY(B2:B,"Select Count(B) where B is not null group by B label Count(B) 'Times'"),"Select Col1, count(Col1) group by Col1 label count(Col1) 'Count'")

Upvotes: 1

Ike
Ike

Reputation: 13064

I would work with a helper column (D) to count visits per person. Then it is pretty easy to count the "x times".

Values in column F are numbers formatted as "0 "time""

enter image description here

Upvotes: 0

Related Questions