How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, BS Degree in Engineering.
Category: General
Satisfied Customers: 9666
Experience:  Math, science and 25 years experience in computer programming
Type Your Question Here...
John D is online now
A new question is answered every 9 seconds

can I produce a countif formula that counts the number of

This answer was rated:

<p>can I produce a "countif" formula that counts the number of cells in a range by colour? i.e. cells D1:D300 have 140 orange, 100 red and 60 green and I wish to summarise. Many thanks</p><p> </p><p>Apologies, this is an Excel question</p>



Yes that can be done with a user defined function or macro.


Hope this helps. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution


John D and 28 other General Specialists are ready to help you
Customer: replied 6 years ago.
Hi - thanks for this but are you able to let me know/illustrate how I can do this? Thanks Karen

I am afraid that requires quite a bit of work. You will need first to accept my answer since it has addressed your original question, then open a new question with appropriate amount for doing the macro





Customer: replied 6 years ago.
Hi - the first answer has been accepted so if you are able to outline the formula required it would be appreciated. Many thanks Karen

Thanks Karen


I need your file so I can set up the macro on.


To do that, go to and upload the file there (no need to sign up). You will then get a page that has the download link and File ID. Copy the download link or the File ID and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it



Customer: replied 6 years ago.

thanks but for some reason the upload won't work. I've tried to resolve the query in another way, rather than colours look up and group the values. I have managed for two:





However, the ones I cannot get to work is the Countif for those that fall between 0-60 - could you please let me have the quick formula for that and hopefully it will resolve my whole issue and I can forget the macro for counting colours (for now). Thanks

Ok, here you go




Hope this helps


Customer: replied 6 years ago.

am really sorry but that is coming up with FALSE. is the above not a subtraction formula? I need to count the number of instances where the value falls between zero and 60


Karen it should work if you have numbers in that range


Here is a sample file with the formula in the yellow cell


Did that work?



Customer: replied 6 years ago.

Hi - sorry for delay. I have no idea why but it's not working on my file. Have managed to upload my file to :


If you can see why I've done it wrong again, if not I'll close out on this query and give up. Thanks

Hi Karen


Here you go

(the download button is the blue button is at the bottom of the page)


The modified formula is in cell G3


Hope this helps


John D and 28 other General Specialists are ready to help you