count or sort on cell formatting

Hi everyone

I've attached to this thread an image which I hope will assist in explaining what I am looking to achieve. I'd be most appreciative of any help you could provide.

In cell A4 I would like to have the spreadsheet automatically calculate the number of blue cells contained in the range D4:I4 (in this example, 3). In cell B4 I would like it to calculate the number of brown cells in the same D4:I4 range (in this example, 1).

Conditional formatting has been set up to automatically turn the cells in the D4:I4 range the same colour of the cells that they match in the range C1:H1 and these numbers (C1:H1) are altered frequently.

I suppose what I'm asking is whether there is some way to total (at A4) the number of blue cells in D4:I4 by some kind of format match function, or would it be easier to have the formula / function at A4 search the range D4:I4 for the number of times any of those numbers match any of the numbers C1:H1 wherever they're placed, and if so, how would this be done / entered?

I hope that all made sense, as I'm not particularly good at explaining these kinds of things ... please let me know if it hasn't :-)

Thanks in advance once again for any suggestions you might have; they will certainlly help out big time.

Sue :-)

Welcome to the forum.

There is no way in Excel to count or sort on cell formatting, so you should add some helper cells to identify the condition that you use to format the cells. It could be done with VBA, but don't add VBA code to your worksbook if you don't have to.

My attachment show you how it could be done. You should/could hide rows 7 to 11.

You could skip the helper cells, and make a arrayformula instead, but array formulas can be difficult to understand, so I made it this way for you to better understand how it works. And the helper cells are also used for setting the conditional formatting.

Thanks Bjornar ... your answer has provided me with just what I'm looking for, and has resolved my query.

Once again, many thanks!

Sue :-)

Thanks for responding to my question - I very much appreciate it, however I have since finalised the spreadsheet using Bjornar's method. Thanks once again for your time and suggestion though; all the very best.

Sue :-)