Shading areas off a countif

[exceljunky]

what i have done is made a grid 10x10 and in that grid are a combination of the letters a, b and c.

im using a countif to count how many a's, b's and c's exist in the grid. The abc's represent seats in a cinema.

Lets say there are 20 seats of type A. I want to start selling tickets for A but colourise seats sold automatically. How would i do this?

eg

in my spread sheet i will have a cell which says no of A seats available and in this case the answer is 20. When i enter 12 tickets sold in another column, i want 12 of the 20 seats to go red in the grid. it doesnt matter which seats are coloured so as long as the sold seats are indicated on my 10x10 grid.

How would i do this? Can it be done?

[NBVC]

Here's one way.. this will highlight all "a's" starting at top left-most cell go to the top-right most and then continuing to next row until desired count is met.

First, make sure you start at minimum, row 2...

So say your matrix is in A2:J11... and your input letter "a" is in L1 and your desired count is in L2...then

select A2:J11 and go to Format|Conditional Formatting and select Formula Is from 1st drop down and enter following formula:

AND(A2=$L$1,COUNTIF($A2:A2,$L$1)+COUNTIF($A$1:$J1,$L$1)<=$L$2)

Click Format and choose colour pattern.

[exceljunky]

woah . . need 5 to see whats happening. Thanks a lot for this. will go and experiment

[exceljunky]

no i couldnt get it to work. ive attached my sheet

[NBVC]

You have 100 in L1, should be an "a", no? If you put an "a" in L1, it works as intended.

You also added to the conditional formula a +$C$2?

Formula in CF should be:

=AND(A2=$L$1,COUNTIF($A2:A2,$L$1)+COUNTIF($A$1:$J1,$L$1)<=$L$2)