+ Reply to Thread
Results 1 to 11 of 11

How to count how many cells have been conditionally formatted?

  1. #1
    Registered User
    Join Date
    03-16-2021
    Location
    North East England
    MS-Off Ver
    O365
    Posts
    4

    Question 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.
    Attached Files Attached Files

  2. #2
    Forum Contributor shank_mis's Avatar
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    128

    Re: How to count how many cells have been conditionally formatted?

    In Cell L2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Confirm with Ctrl + Shift +Enter
    Shashank Mishra
    Please hit "Add Reputation" Button if you liked the answer.

  3. #3
    Registered User
    Join Date
    03-16-2021
    Location
    North East England
    MS-Off Ver
    O365
    Posts
    4

    Re: How to count how many cells have been conditionally formatted?

    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
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: How to count how many cells have been conditionally formatted?

    Please explain the logic behind this. Each Lotto draw is standalone, so if someone gets six numbers right, what significance does a previous draw have? Without the logic, we can't build a solution.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: How to count how many cells have been conditionally formatted?

    Posting to older threads (hijacking) is not allowed and is not going to get your query answered any faster. Concentrate on responding to those of us offering help here. Your definition of what you want is deficient at the moment.

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    2,008

    Re: How to count how many cells have been conditionally formatted?

    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

  7. #7
    Registered User
    Join Date
    03-16-2021
    Location
    North East England
    MS-Off Ver
    O365
    Posts
    4

    Re: How to count how many cells have been conditionally formatted?

    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!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: How to count how many cells have been conditionally formatted?

    Right - the first paragraph was what we needed. Thank you!

    Going to have a think ...

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,457

    Re: How to count how many cells have been conditionally formatted?

    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

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: How to count how many cells have been conditionally formatted?

    See if this does it:

    =SUMPRODUCT(--(COUNTIF($N$2:$S$23,B2:K2)<>0))

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,674

    Re: How to count how many cells have been conditionally formatted?

    Self deleted
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count cells conditionally formatted by colour
    By ilohyou in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-05-2018, 12:01 PM
  2. [SOLVED] Count cells that have a conditionally formatted background
    By FivestarMac in forum Office 365
    Replies: 3
    Last Post: 10-26-2016, 04:55 AM
  3. Count conditionally formatted cells in excel
    By katiejessop in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-17-2013, 11:19 AM
  4. [SOLVED] How To Count Conditionally Formatted Cells
    By ScotyB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-16-2013, 07:51 PM
  5. UDF to Count Conditionally formatted cells by colour
    By ozzy_q in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2013, 09:00 AM
  6. [SOLVED] count conditionally formatted cells
    By littlefoot in forum Excel General
    Replies: 5
    Last Post: 07-12-2012, 08:40 AM
  7. Replies: 1
    Last Post: 08-26-2011, 09:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1