Is there a formular in Excel that can count the colours of a cell?

Hi Everyone,

I was wondering can excel count the number of cells have a particular fill colour?

Its mainly to have a summary of urgent, medium and low priorities, which are highlighted by colour- so the text within each box will vary.

Thanks for your help with this.

PS. I'm using Excel 2007.

use this UDF:

Formula:

Please Login or Register  to view this content.

I put your data vertically so you can easily drag formula down.

I've also added SumByColor in same workbook, just in case you need it

Your excel has Macro based file. Is this needed for this function?

You could use filter and filter out colored cells (Excel 2007+) and then use SUBTOTAL function to count remaining rows...
But other than that you need a macro (can't be done with regular formula).

1. Do you have some sample for filter and filter out colored cells (Excel 2007+) and then use SUBTOTAL function to count remaining rows...

2. Where I see your settings for Macro? I see only function or I have to store into your format extension?

1. Check example above
2. in macro module: click Alt+F11 to open VB editor
3. if you have some further questions, acoording to Rule 2 of our Forum [/I]RULES -- start your own thread. If you feel it's particularly relevant, provide a link to this (or other) thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.