count how many time a value is in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-1512807%22%20slang%3D%22en-US%22%3Ecount%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512807%22%20slang%3D%22en-US%22%3E%3CDIV%3E%26nbsp%3B%3CSPAN%3EI%20have%20list%20of%20names%20and%20%26nbsp%3Ba%20number%20near%20each%20name%20in%20a%20other%20column%20i%20want%20to%20know%20how%20many%20ppl%20i%20have%20from%20each%20number%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esomething%20like%20this%26nbsp%3B%3C%2FP%3E%3CP%3E1%2070%20ppl%26nbsp%3B%3C%2FP%3E%3CP%3E2%2012%20ppl%3C%2FP%3E%3CP%3E3%2015%20ppl%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20idea%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512807%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512845%22%20slang%3D%22en-US%22%3ERe%3A%20count%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3B%20lets%20say%20you%20have%20names%20in%20A%20and%20categories%20in%20B%20and%20you%20want%20to%20count%20how%20many%20people%20are%20in%20each%20category%20you%20should%20be%20able%20to%20add%20col%20C%20with%20each%20category%20listed%20and%20in%20col%20D%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(%24B%3A%24B%2C%24C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eif%20you%20have%20a%20title%20in%20row%201%20you%20may%20start%20in%20cell%20D2%20and%20change%20that%20to%20%24C2%20before%20filling%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513011%22%20slang%3D%22en-US%22%3ERe%3A%20count%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20clarify%20you%20would%20like%20to%20count%20or%20to%20sum.%20For%20example%2C%20name%20ABC%20has%20two%20records%3C%2FP%3E%0A%3CP%3EABC%2070%20ppl%3C%2FP%3E%0A%3CP%3EABC%2020%20ppl%3C%2FP%3E%0A%3CP%3Eresult%20shall%20be%202%20or%2090%20ppl%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513053%22%20slang%3D%22en-US%22%3ERe%3A%20count%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22226%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22162%22%3Ename%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ERate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20a%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20b%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20c%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20d%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20e%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ename%20f%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20know%20how%20many%20ppl%20I%20have%20rate%201%20how%20many%202%20total%20numbers%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22128%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ERate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETotal%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3D%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513169%22%20slang%3D%22en-US%22%3ERe%3A%20count%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3Byou%20build%20a%20pivot%20table%20that%20groups%20by%20rate%2C%20and%20counts%20instead%20of%20sum%20(you%20select%20the%20formula%20to%20use%20for%20grouping).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513337%22%20slang%3D%22en-US%22%3ERe%3A%20count%20how%20many%20time%20a%20value%20is%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723849%22%20target%3D%22_blank%22%3E%40hershel13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20for%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20700px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204508iD1F8E10F629953D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCHOOSE(%7B1%2C2%7D%2CUNIQUE(%24C%243%3A%24C%248)%2CCOUNTIF(%24C%243%3A%24C%248%2CUNIQUE(%24C%243%3A%24C%248)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

@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

Highlighted

@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?

Highlighted

@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



=

Highlighted

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

 

Highlighted

@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)))