Calculating which name occurs most in a column

[kreases]

Hi,

I have catalogued my record collection and wondered if there is any way to find out which Artist's name occurs most in the Artists column, I don't require a count as such I just want to put the name that occurs most in another cell for stats purposes, any ideas?

 

[PritishS]

Hi,

Put this formula to any cell.

=COUNTIF(C2:C1000,"ARTISTNAME")

Considering, C-column as 'Artist's Name' and C1 as heading. Adjust the range and put the Artist's name in place of "ARTISTNAME"

Regards,
PritishS

 

[kreases]

Hi,

That formula is only going to count how many times a 'named' artist appears in the column what I need is to be told which Artist appears the most in the Artists column, there are a lot of different artists names in that column, I don't want to have to count each one I want a formula that will return the name of the Artist that appears most, does that make sense?

 

[AlanY]

have a look here with the mode() function

https://www.extendoffice.com/documents/excel/1581-excel-find-most-common-value.html

 

[PritishS]

AlanY,

Good Link. Learnt a new thing. Thanks

 

[cunningAce]

You could use this formula,

=INDEX(A2:A9,MATCH(LARGE(B2:B9,1),$B$2:$B$9,0),1)

Artists names in column A, you would need a count function column, Column B for this formula.

 

[AlanY]

AlanY,

Good Link. Learnt a new thing. Thanks

you're welcome

 

[kreases]

have a look here with the mode() function

https://www.extendoffice.com/documents/excel/1581-excel-find-most-common-value.html

This works, the only issue I have is if there are more than one Artist appearing the same amount of times it only highlights the first that occurs.

I have two Artists with 8 singles (Bee Gees and Al Green) the formula came up with Bee Gees only

 

[AlanY]

This works, the only issue I have is if there are more than one Artist appearing the same amount of times it only highlights the first that occurs.

I have two Artists with 8 singles (Bee Gees and Al Green) the formula came up with Bee Gees only

try this in C1 and copy down, it will ranked them from top1, 2 etc

Excel 2013/2016
	A	B	C
1	FFF		RRR
2	RRR		UUU
3	BBB		VVV
4	HHH
5	WWW
6	RRR
7	OOO
8	RRR
9	MMM
10	KKK
11	RRR
12	ZZZ
13	UUU
14	BBB
15	ZZZ
16	UUU
17	RRR
18	VVV
19	VVV
20	UUU
Sheet2

Cell Formulas
Range	Formula
C1	{=INDEX($A$1:$A$20,MATCH(LARGE(FREQUENCY(MATCH($A$1:$A$20,$A$1:$A$20,0),ROW($A$1:$A$20)-ROW($A$1)+1)+ROW($A$1:$A$21)/10000,ROWS(C$1:C1)),FREQUENCY(MATCH($A$1:$A$20,$A$1:$A$20,0),ROW($A$1:$A$20)-ROW($A$1)+1)+ROW($A$1:$A$21)/10000,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[barry houdini]

Here's another possibility.....

With data in A1:A20 and a blank or header in D1 use this array formula in D2

=INDEX(A$1:A$20,MODE(IF(COUNTIF(D$1:D1,A$1:A$20)=0,{1,1}*MATCH(A$1:A$20,A$1:A$20,0))))

confirm with CTRL+SHIFT+ENTER and copy down