How can I use Countif to count by fill color?

thejesus

New Member
Joined
Jan 12, 2005
Messages
6
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?!)
 
Upvote 0
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! :oops:
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,043
Messages
6,163,565
Members
451,844
Latest member
ddnndd1234

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top