+ Reply to Thread
Results 1 to 3 of 3

Need formula to count cells of a specific color that contain specific text

  1. #1
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Need formula to count cells of a specific color that contain specific text

    Hello all!

    I am sure the solution to this is very simple.

    In a workbook, column F contains the initials of our team members. If that team member participated in Activity A, the cell containing their initials turns pink. If they participated in Activity B, the cell containing their initials turns teal.

    In another sheet on the workbook, I need a formula to count the initials and the color.
    This is what I was trying:

    =CountCellsByColor('L1 TM'!$F$24:$F$5000,B25) & IF('L1 TM'!$F$24:$F$5000,"ABC")

    B25 is the cell where the color is as a reference.

    But the formula doesn't work. Can anyone help me out??

    Thank you!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need formula to count cells of a specific color that contain specific text

    Hi hadydea,

    If you give us a sample workbook it is a much easier problem. Excel really doesn't work well with cell colors as the color isn't "data". That being said, here is a website that claims to have an answer for you.

    https://trumpexcel.com/count-colored-cells-in-excel/
    There is also some code on the below site that claims to do the same.
    https://www.ablebits.com/office-addi...y-color-excel/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Need formula to count cells of a specific color that contain specific text

    To be clear, I already have a function that counts the cell color.

    I've attached a sample workbook.

    In the workbook, on page L1 TM, Column F has the initials ABC in it. the color of the cells after it determine what color column F will turn. If all the cells are either yellow or green, the initials cell turns pink. If some of the cells are red or grey, the initials cells turn orange.

    On the Project Totals page, I need a formula that counts pink cells containing the initials ABC and a formula that counts orange cells containing the initials ABC.
    There are multiple initials that will be used, which is why I need a formula and can't just use the count color that you suggested.
    Attached Files Attached Files

+ 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. Count cells with specific color and text
    By sky9056 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2019, 03:07 AM
  2. Replies: 10
    Last Post: 01-16-2018, 12:51 AM
  3. [SOLVED] Formula to count adjacent cells containing specific text
    By BuddJ in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-17-2017, 10:14 PM
  4. A formula to count cells with a specific color?
    By Harlort in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2014, 08:55 AM
  5. Replies: 3
    Last Post: 02-16-2013, 06:10 PM
  6. Replies: 2
    Last Post: 07-31-2012, 01:11 PM
  7. Excel formula to count cells with specific text
    By timtrace in forum Excel General
    Replies: 3
    Last Post: 04-06-2011, 12:32 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