Recent Feedback
<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>
Already Tried: nothing
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
Experience: Math, science and 25 years experience in computer programming
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
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 www.wikisend.com 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
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,"<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
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?
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