Can I count the number of cells in a row that have a specific format?

**M4rk** · 11-08-2012, 10:50 AM

I am trying to find out if I am able to count the number of occurrences of a highlighted cell (or cell with colored text) within a row or worksheet. Is this possible in Excel? I've looked into the +CELL("color") function but this seems inappropriate for what I'm after.

**zbor** · 11-08-2012, 11:01 AM

Does this help:

http://www.excelforum.com/excel-form...=1#post2995075

**M4rk** · 11-09-2012, 06:49 AM

Hi Zbor, thanks for your reply but I don't think it helps in my case.

My worksheet is formatted with days of the month for column headers and rows for various activities. Members names are entered into cells corresponding to the day and activity attended. and as such can appear anywhere on the sheet multiple times. What I am trying to to is to show whether any of the members attending one of the events received a "Merit", "Warning" or were "Ejected" by highlighting the cell Blue, Orange or Red and then report how many times any member has received any one of these 3 behavioral markers. See snapshot attached.
kaxlscrop.jpg

**zbor** · 11-09-2012, 07:31 AM

Why don't you count those words instead of looking for color?

For example =COUNTIFS(range1, name, range2, "Warning")+COUNTIFS(range1, name, range2, "Merit")+COUNTIFS(range1, name, range2, "Ejected")

**M4rk** · 11-09-2012, 09:16 AM

Thanks Zbor, I was trying to get away without having to create additional columns to record these behaviors for each day of the month but looks like I'll have to go down this route and use the COUNTIFS function to return what I'm after. Looks like this is solved now given my quick tests but have got a lot of work to do to create up to 62 ranges and then copy that across all months and members. Will update this thread when done, probably next week given commitments.

**M4rk** · 11-09-2012, 07:26 PM

Whilst what I was trying to do was achievable using a COUNTIFS formula with 64 ranges this was taking so long to compute just 1 column let alone the other 300 columns that excel was unusable. So I decided to create another worksheet with a simple 4 column table to record the additional information I wanted rather than adding columns to my already large main monthly worksheets. Whilst this means the users will have to enter this particular information into a separate sheet rather than on the relevant monthly sheets, the payoff in terms of usability outweighs this need. I've then been able to use simple 2 range countifs on the main members database to show the relevant info.

Thanks again