+ Reply to Thread
Results 1 to 6 of 6

Count based on color of cell

  1. #1
    Registered User
    Join Date
    01-07-2015
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Count based on color of cell

    My excel spreadsheet has different colored rows based on certain dates.
    I want to count the number of rows that are green, count the number of rows that are yellow and count the number of rows that are red. This count would be entered into a table at the bottom.

    So what is the formula to count the rows that are a certain color?

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Count based on color of cell

    Hello
    There is as such no formula for counting colored cells however if you can give any other criteria based on cell value then we can certainly count the no. of rows
    Thanks
    Regards
    Sourabh

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on color of cell

    Is there some logic to how the cells are colored? It kinda sounds like you may even have used Conditional Formatting to color them?
    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

  4. #4
    Registered User
    Join Date
    01-07-2015
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Count based on color of cell

    Yes, I did use contidional formatting to color them based on dates.

    Ok so I have a date..its the date something is due. If the due date has passed or if the due date is within 30 days, the row is red. If the due date is within 60 days, the row is yellow. If the due date is within 90 days or more it is green.

    So I need to track how many rows are red, yellow and green based on the due date I guess. At the bottom the table is tracking this and I want the count to change when the due date changes.

    Hope that makes sense...
    Attaching the spreadsheet for a better idea...
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on color of cell

    THis should give you a start.

    For red...
    =COUNTIF($B$2:$B$22,"<="&TODAY()+30)

  6. #6
    Registered User
    Join Date
    01-01-2015
    Location
    England
    MS-Off Ver
    Office 13
    Posts
    4

    Re: Count based on color of cell

    Hi

    I have used this before, is this the sort of thing you meant??

    if you hit Alt F11 you will see the coding added to the module.

    Hopefully allowed to post this as appreciate it is work available in the public domain

    Source :_ https://www.ablebits.com/office-addi...y-color-excel/
    Attached Files Attached Files
    Last edited by stuarttp; 01-12-2015 at 06:15 PM.

+ 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] How to COUNT cell based on either COLOR or correct result?
    By Kagesen in forum Excel General
    Replies: 3
    Last Post: 05-12-2014, 12:40 AM
  2. Count cells based on cell color
    By vamshi57 in forum Excel General
    Replies: 3
    Last Post: 04-14-2014, 02:11 AM
  3. Count based on cell fill color
    By hmesler in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-29-2011, 01:01 PM
  4. Changing cell color based on count
    By mossman65 in forum Excel General
    Replies: 1
    Last Post: 01-28-2011, 09:37 PM
  5. macro: changing cell color based on count in cell
    By Vbort44 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2008, 07:28 PM

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