How to count how many cells have been conditionally formatted?

Hi everyone,

I am trying to create a lotto bingo tracking spreadsheet based on another post on here: 709954-excel-2007-lotto-check-sheet

So at the moment, I have all players numbers conditionally formatted to turn green if they match one of the lotto numbers which have been drawn. However, similarly to the example in the linked thread, I wish to count how many correct numbers each player has.

Any help is greatly appreciated.

Hi,
Unfortunately, that hasn't worked because if a number comes out in two different weeks, it will count the number twice. Whereas, if the player has already had that number once, it shouldn't be counted again. Please see the updated file containing the next couple of weeks - as you can see for 'Daphne' she only has 4 numbers but both 26 and 49 have appeared twice so it has counted 6 correct numbers'

Can it be changed so that it only counts once?

Thanks,

Kate

L2=IF($A2<>"",SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($N$2:$S$6,B2:K2,0)),$N$2:$S$6),$N$2:$S$6),1)),"")

Control+shift+enter

copy down

Hi,

Before the 13th of March 2021, each player picked 10 numbers of their choosing. The 6 numbers to the right of this are the lotto numbers that are drawn each week.
Each week, if any of the numbers drawn matches a player's pre-selected number, it gets marked as 'correct'. The winner is the first person to have all 10 of their numbers drawn.

So players aren't looking to match with 6 numbers in one week, they are looking to have all 10 of their numbers selected over multiple weeks (like a bingo draw where you want to mark all of your numbers off). This can go on for many weeks depending on how long it takes for someone to win.

However, the way that the lottery works is that each week is a standalone as you say, so all of the numbers are put back into the pool with a chance of being drawn again. But if a number has already been drawn in a previous week, it shouldn't be counted again. I.e. the user Daphne has 4 correct numbers as shown in green however, in cell L2 it says she has 6 correct - because it has counted #26 and #49 twice (which I don't want).

I hope I've explained clearly enough, any questions please ask!

Try this array* formula instead in L2:

=SUM(IF(COUNTIF($N$2:$S$23,B2:K2)>0,1))

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter, although I have read that this is not necessary in Office 365.

Hope this helps.

Pete