Excel: Conditional Formatting Colour in reverse?

[Sylph]

Here is the scenario: I am trying to keep track of winning keno numbers, I have several hundred people, all have picked 8 numbers. This information is listed across a row for each person (Name in column A, numbers from *B2:I2 for example).
20 numbers are called and I have setup a conditional format that will highlight the winning numbers in red as I enter them in another column (M). I did this as follows:
Selected number range from B2 to I1000, chose Conditional Format, used this formula =COUNTIF($M$2:$M$21,B2)>=1, I clicked Format, then selected the colour RED. This has worked fine, when I type in a selection of numbers in the M column, the corresponding numbers for those people who have chosen those numbers are highlighted in red.

Now, here's my problem. I have created a column J immediately after the players numbers and in this column I would like to be able to tally how many of the numbers in the player's selection has been highlighted RED. Let's say out of the 20 numbers called, BillyBob has 4 of those numbers. They have already been highlighted, but to save scrolling down the list to try to pick them out, I would like a tally to appear in column J, preferably in another colour, but that's not paramount (don't care what other colour). BUT, if possible, I would only like a tally if the player has either 0 (zero) numbers or 3 or more (1 and 2 numbers don't count). Am I asking too much? Or can it be done please?

Thanks for your time
Cheers
Sylph

 

[combo]

You have no possibility to check colour of conditionally formatted cell - even using VBA you always receive the basic format.

To solve your problem, I would do the following:
1. to clear formulas name the range with winning numbers, for instance win_list (Insert>Name>Define...).
2. use array formula in J column: {=SUM(COUNTIF(B2:I2,win_list))} to count hits. To get array insead normal, hold CTRL and SHIFT keys when hitting ENTER. (The {} brackets are visible only in the formula bar; you do not write them, they are created after enteriing with CTRL+SHIFT keys and are destroyed while normal editing.)
3. Use conditional formatting for column J (0, 1-2, >2).

 

[Sylph]

Ok, thank you for that Combo, I'll have a fiddle around with it and see if I can get it right

Cheers
Sylph