count how many time a value is in a column

Copper Contributor
 I have list of names and  a number near each name in a other column i want to know how many ppl i have from each number 

 

something like this 

1 70 ppl 

2 12 ppl

3 15 ppl

 

any idea ?

5 Replies

@hershel13  lets say you have names in A and categories in B and you want to count how many people are in each category you should be able to add col C with each category listed and in col D use:

=COUNTIF($B:$B,$C1)

if you have a title in row 1 you may start in cell D2 and change that to $C2 before filling down

@hershel13 

Please clarify you would like to count or to sum. For example, name ABC has two records

ABC 70 ppl

ABC 20 ppl

result shall be 2 or 90 ppl?

@hershel13  

name Rate
name a1
name b1
name c1
name d1
name e2
name f2

 

I want to know how many ppl I have rate 1 how many 2 total numbers 

 

RateTotal 
14
22



=

@hershel13 you build a pivot table that groups by rate, and counts instead of sum (you select the formula to use for grouping).

 

@hershel13 

As variant for

image.png

formula is

=CHOOSE({1,2},UNIQUE($C$3:$C$8),COUNTIF($C$3:$C$8,UNIQUE($C$3:$C$8)))