• 100% Satisfaction Guarantee
John D, BS Degree in Engineering.
Category: General
Satisfied Customers: 9666
Experience:  Math, science and 25 years experience in computer programming
53785
John D is online now

# can I produce a countif formula that counts the number of

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

Hi,

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

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.

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:

=COUNTIF(\$G\$10:\$G\$3112,">60")

=COUNTIF(\$G\$10:\$G\$3112,"<0")

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

=COUNTIF(\$G10:\$G\$3112,">0")-COUNTIF(\$G\$10:\$G\$3112,"<=60")

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

http://www.2shared.com/document/lhYzA1uw/countif.html

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 :

http://www.2shared.com/file/V9YjIZq4/11-02-18_RC_Governance_Logs_v1.html

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

http://www.2shared.com/file/68jZ9elp/11-02-18_RC_Governance_Logs_v1.html

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

The modified formula is in cell G3

Hope this helps