How can I use Countif to count by fill color?

[thejesus]

I’m trying to find a solution that will allow me to use the “countif” function to count the number of cells in a predefined range that contain a specified fill color.

For example, if I have a column of 100 cells and 45 of them are red, I’d like to be able to type in a function that will give me the number “45” when I tell Excel to count how many of the 100 cells are red cells.

Please help!

 

[Zack Barresse]

Hi there, welcome to the board!!


Here is a link that has an example and a downloadable example spreadsheet, along with directions ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=89

Chip Pearson also has an excellent solution here ...

http://www.cpearson.com/excel/colors.htm


HTH

 

[Greg Truby]

Also, here's a link to Chip's page that offers several examples of UDF's you can use.

{Edit}Hiya Zack -- did you edit that while I was typing? I swear I didn't see the link to Chip's site in your response before I started typing! (Or am I getting blind(er) in my old age?!)

 

[Zack Barresse]

.. {Edit}Hiya Zack -- did you edit that while I was typing? I swear I didn't see the link to Chip's site in your response before I started typing!

Yup! Had a mishap cutting and pasting!

 

[thejesus]

Hey thanks for the info...you guys really pointed me in the right direction...

I am somewhat familiar wtih VB but I have never incorporated an VB script into Excel before...

Am I supposed to copy the VB code into a module in the VB editor in Excel and then just call up the function like I would any other function?

 

[yee388]

Am I supposed to copy the VB code into a module in the VB editor in Excel and then just call up the function like I would any other function?

Exactly.

 

[thejesus]

hmm....I just get #Name in the cell that I place the formula in

 

[Zack Barresse]

Where did you put the code?
How are you calling the function?
Can you post an example using the HTML Maker? (link at bottom of page)

 

[thejesus]

hmmm...well it looks like I got it working but for some reason any changes I make to the spread sheet aren't reflected in the formula total until I save, close, and restart the spreadsheet...

What I was doing before was working but I didn't realize it since the changes didn't take effect immediately

any idea why this is?

 

[Zack Barresse]

Well, if using the color function, it won't change unless a change is made to the cell dependents. And changing the color of a cell does not constitute a change, in Excel's eye's. Right after the first "Function ... (..)" line, put this ..

Application.Volatile

This will make the function calculate everytime a calculation effort is made - no matter what. Make sure and read the help files on that command also.