Conditonal Formatting a list within a list

[Mirokilili]

Hello,

I have a long list of books sorted by author. I need to highlight the authors with 10 or more books. I have a column that shows the count for each author using this formula:=IF(C3=C2,H2+1,1) where column c holds the authors names and column H counts each book for the author and starts over at 1 when a new author name appears.

SO I would like to highlight either the consecutive numbers from 1 to the last number for the author when the last number is greater than 9 or the entire rows for those authors.

I appreciate any help.

 

[Eric W]

Welcome to the forum.

Select the columns you want to highlight. Click Conditional Formatting > New Rule > Use a Formula > and enter:

=SUMIF($C:$C,$C1,$H:$H)>45

Click Format and select a fill color, click OK.

Hope this helps.

 

[Mirokilili]

Thank you Eric for the reply. Can you explain what your formula does?

My list has almost 7000 entries and some authors have less than 10 books. I want to highlight only the authors and their books if they have 10 or more in the list so if an author has 20 books I want to highlight the entire range of 20 books but if an author has 9 books, they will not be highlighted.

Will this formula do that?

 

[Eric W]

Yes it will. It just adds up your counter in column H by author. 1+2+3+4+5+6+7+8+9 = 45, so if the sum by author is greater than 45, then that author must have at least 10 books.

Another option:

=ISNUMBER(MATCH($C1&"10",$C$1:$C$10000&$H$1:$H$10000,0))

This just looks to see if the author on the current line has a 10 in column H. With 7000 rows, you start to think about performance. If your workbook starts to slow down with the previous formula, try this one. But this one requires you to put in an actual row count. I can't say offhand which is more efficient, but try the SUMIF first.