Conditonal Formatting a list within a list

Mirokilili

New Member
Joined
May 15, 2018
Messages
2
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top