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!

 

[thejesus]

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.

actually, application.voliatile is already part of the code I copied from the site you gave....

I had one other question about this you might be able to help me with...

When I call up the function in excel, I currently have =COUNTBYCOLOR(D1:D10,6,FALSE) to count all the yellow cells from D1 to D10....

My question is, can I select multiple ranges to be counted? Is there a way to type it I could, say, count cells D1 to D10 and also D15 to D20 at the same time?

Thanks

 

[Zack Barresse]

Yes, just put the range in parenthasis and seperate with a comma ...

=COUNTBYCOLOR((D1:D10,D15:D20),6,FALSE)

 

[littleme]

name-error

How did you get the function to work?

Only get name-error in the cell...? All I did was copy-paste... first few line of the code become red in VBA... help?

 

[Zack Barresse]

You are receiving the #NAME error if the routine is not found. It must be in a Standard Module in the same workbook as you are calling it from. If you have it in your Personal.xls workbook, you would need to change the formula to something like this ..

=Personal.xls!COUNTBYCOLOR((D1:D10,D15:D20),6,FALSE)


HTH