+ Reply to Thread
Results 1 to 11 of 11

Formula to count coloured cells

  1. #1
    Forum Contributor
    Join Date
    04-30-2011
    Location
    wirral,england
    MS-Off Ver
    Excel 2010
    Posts
    144

    Formula to count coloured cells

    Hello All
    I have a column of data in Col K,I would like to use a formula which will count the data as follows
    A.number of cells in red font on blue background
    B.number of cells in red font total
    C.number of cells in green font on blue background
    D.number of cells in green font total
    E.number of cells in black font on white background
    F.number of cells in black font total
    The results for each in sequence to be in Cols b&c rows 1 to 3
    eg.Red font cells on blue background = 208
    Red font cells total = 673
    The formula will then count the numeric data in Cols P&Q relating to each colour combination in Col K and place the results in Cols N&O rows 1 to 3.
    eg.Total for red font on blue background in col P =2800
    Total for red font on white background in col Q =19514
    I have attached a worksheet which i hope will explain,many thanks for help given.
    Attached Files Attached Files

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to count coloured cells

    This can't be done with formulae, you'll need VBa code, or a UDF.

    There are no native functions that can distinguish cell colours, or font colours.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: Formula to count coloured cells

    colour is not data.

    If you manually colour cells, you don't add any information to the sheet that Excel can use. In later versions, you can filter by colour, but that does not do much good. You still cannot count or sum by colour.

    Instead of manually colouring cells and then trying to find a way to summarise that data, take the better approach:

    Use another column to enter a value that applies to the data in the row. Then use conditional formatting to colour the fill and font based on that hard and fast piece of data.

    Now it's easy to report on the data, since there is a value that can be used for summing or counting.

    So, for example, in a new column enter a 1 for all values with red on blue, enter a 2 for all values with red on white, etc. Then format all values to be black on white. Use conditional formatting to colour the original values, if the new column = 1, colour red on blue, if the new column = 2 colour red on white, etc.

    Now you can also use Sumif/Sumifs or Countif/Countifs to report on the data, based on the new column.

    And finally, the data sample would have been sufficient with 10 rows per colour set. I gave up trying to edit your sheet after I hit row 1500. Make it easy for us to help you. A small data sample will do. We don't need 3000 rows to understand what you want to achieve if you give clear specifications.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Formula to count coloured cells

    Or you can make a trick and filter colored values:

    Click drop down arrow by "test" -> Filter by Color

    Then you'll get a number of filtered cells...
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    Re: Formula to count coloured cells

    Hi,
    May be a solution with using the macro function of excel 4
    The macro function is only available in the defined name from excel 2007
    The syntax is Read.Cell(no_type;reference)
    To read Font Color first caracter no_type is 24 and to read Background Color no_type is 63
    You can hide the G and H column
    Hope this helps
    Best regards
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,436

    Re: Formula to count coloured cells

    if you have some "function" that relates to what colors are applied, you base your count on that?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to count coloured cells

    @ jpr73 Why use Excel Macro4, and not VBa ...

    In a standard module
    Please Login or Register  to view this content.
    Call as UDFs
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Drag/Fill Down

  8. #8
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: Formula to count coloured cells

    Rather than using tricks and macros, I strongly advocate good practice worksheet design.

    You can design a bad worksheet layout and then engage a fleet of helpers to pull you out of your dilemma. Next time around, you'll be in the same situation. Or if you need to add another color combination to the sheet. Or if a rule changes. Or ..., or ..., or ...

    Save yourself the trouble and use a solution you can maintain.

    Add a column to the data. Enter a code for each color combination, for example

    1 -- red font on blue background
    2 -- red font total
    3 -- green font on blue background
    4 -- green font total
    5 -- black font on white background
    6 -- black font total

    Then use conditional formatting to color the cells.

    Summing and counting of the different colors is now easy. Use Sumif, Countif or a pivot table.

    Again: color is not data. Trying to make it into data is more work than actually adding the data to the sheet.

  9. #9
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    Re: Formula to count coloured cells

    Hi,
    @ Marcol
    I never learned VBA, however it may not be too late
    I get strange values ​​from line 1461 to line 3193
    When using your code and the 2 UDFs function, the result of the font color for black shows the value of -4105 instead of 0 with Excel 4 macro ?
    The result for the UDFs CellColor is OK
    How can I fix it ?
    Best regards

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to count coloured cells

    This is because the font colour is set to automatic (-4105), and your default is black (ColorIndex = 1)

    Change the function to this
    Please Login or Register  to view this content.
    To see the range of 56 palette colours run this macro in your sample sheet.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    Re: Formula to count coloured cells

    Hi
    @ Marcol
    Now everything is clear for my side, I started to learn some VBA
    Thanks you for sharing

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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