# How can I use Countif to count by fill color?

#### thejesus

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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?!)

Greg Truby said:
.. {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!

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?

thejesus said:
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.

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

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)

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?

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 ..

Code:
``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.

Replies
3
Views
150
Replies
4
Views
75
Replies
3
Views
71
Replies
6
Views
137
Replies
3
Views
303

1,196,429
Messages
6,015,214
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back