Conditional format the top x non zero values by column

[John_Whin]

I don't know how to use an IF to select non zero values only for conditional formatting.

Ideally want to highlight the top x non zero values in column B only, then highlight top x non zero in column C only etc. Would like to do this across multiple columns.

Thanks

 

[John_Whin]

I did not exactly understand where to put your formula, and was away for a few days. Right now the new problem is more important. I can live without the conditional formatting nicety better than I can this problem.

Thanks to you both for the help. I really appreciate it.

 

[John_Whin]

OK, now the second problem is corrected please lets start fresh. Cells F3:AL30 do not have any conditional formatting. Those cells are populated by a COUNTIFS function. So I'm going to the Penalty Input sheet, using the player name to count the occurrences of each Penalty type.

Exactly how do I format the columns to highlight the top 3 values in each column while ignoring values of zero? Is there a direct way to do this using Conditional Formatting or do I need to nest the COUNTIFS in an IF statement that says if the result of the COUNTIFS function > 0 then COUNTIFS result is applied, else then have an empty cell?

Sorry for the large image, screenshot from 4K monitor.

 

[Fluff]

Ok, if this is what you want to see

Excel 2013/2016
	F	G	H	I	J
1
2
3	1	0	1	4	0
4	0	0	0	0	0
5	1	1	2	0	0
6	0	1	1	0	0
7	0	0	0	0	0
8	0	0	0	0	1
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	1
12	1	0	3	0	0
13	0	0	0	3	0
14	0	0	1	0	0
15	0	0	1	1	0
16	1	1	2	0	0
17	0	0	0	0	0
18	0	0	0	0	0
19	0	0	2	0	0
20	0	0	0	0	0
21	0	0	1	0	0
22	0	0	0	0	0
23	0	0	0	5	0
24	1	0	2	0	0
25	0	0	0	2	0
26	0	0	0	0	0
27	0	0	0	0	0
28	0	0	0	0	0
29	0	0	0	0	0
30	0	0	1	0	0
Ramp

Select F3:AL30 > Conditional Formatting > New Rule > use a formula > enter =AND(F3<>0,(SUMPRODUCT(1-(F$3:F$30=0),--(F$3:F$30 > F3))+1) < 4) > select format > ok

 

[John_Whin]

Leaving for a while, that is exactly what I want to do. Will work on it later.
Thanks

 

[John_Whin]

Works perfectly, just what I counted on from this forum's expertise.

Thank you very much Fluff. Wish your next pint could be on me.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Peter_SSs]

Works perfectly,

In that case it looks like you could also use this

Excel Workbook
	F	G	H	I	J
1
2
3	1	0	1	4	0
4	0	0	0	0	0
5	1	1	2	0	0
6	0	1	1	0	0
7	0	0	0	0	0
8	0	0	0	0	1
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	1
12	1	0	3	0	0
13	0	0	0	3	0
14	0	0	1	0	0
15	0	0	1	1	0
16	1	1	2	0	0
17	0	0	0	0	0
18	0	0	0	0	0
19	0	0	2	0	0
20	0	0	0	0	0
21	0	0	1	0	0
22	0	0	0	0	0
23	0	0	0	5	0
24	1	0	2	0	0
25	0	0	0	2	0
26	0	0	0	0	0
27	0	0	0	0	0
28	0	0	0	0	0
29	0	0	0	0	0
30	0	0	1	0	0
CF Top 3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3	1. / Formula is =AND(F3<>0,F3>=LARGE(F$3:F$30,3))	Abc

 

[John_Whin]

Thanks Peter, I'll study both formulas. I can understand yours easier than Fluffs.

 

[Peter_SSs]

Thanks Peter, I'll study both formulas.

No problem. Glad to contribute.