Highlight top values based on text in specific column

[smide]

Hello.




In column B (B3:B600) of my Workbook I have a list of different towns (about 25 towns) and in column C (C3:C600) customer names.


In column F (F3:F600) there are buying prices for each customer.


I need to highlight (color red) cells with top FIVE customer names (column C) based on buying price value and town name for each town.


example.

	A	B	C	D	E	F
1
2
3		Town1	Name1			15
4		Town1	Name2			2
5		Town1	Name3			10
6		Town1	Name4			0
7		Town1	Name5			6
8		Town1	Name6			24
9		Town1	Name7			9
10		Town2 (new town)	Name8			7
11		....	...			....

<tbody>
</tbody>

​

highlighted top FIVE customer names for Town1 (based on values in column F): C3, C5, C7, C8 and C9

 

[Misca]

Try using conditional formatting with a formula like

=SUMPRODUCT(--($B$2:$B$101=$B2),--($F$2:$F$101>$F2))<5

The sumproduct counts how many rows with the matching town name have a bigger value in column F.