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: 9658
Experience:  Math, science and 25 years experience in computer programming
53785
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

Resolved Question:

<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>
Submitted: 3 years ago.
Category: General
Expert:  John D replied 3 years ago.

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

 

John D, BS Degree in Engineering.
Category: General
Satisfied Customers: 9658
Experience: Math, science and 25 years experience in computer programming
John D and 127 other General Specialists are ready to help you
Customer: replied 3 years ago.
Hi - thanks for this but are you able to let me know/illustrate how I can do this? Thanks Karen
Expert:  John D replied 3 years ago.

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 3 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
Expert:  John D replied 3 years ago.

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

 

 

Customer: replied 3 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

Expert:  John D replied 3 years ago.

Ok, here you go

 

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

 

Hope this helps

 

Customer: replied 3 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

 

Expert:  John D replied 3 years ago.

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

 

Expert:  John D replied 3 years ago.

Did that work?

 

 

Customer: replied 3 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

Expert:  John D replied 3 years ago.

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

 

John D, BS Degree in Engineering.
Category: General
Satisfied Customers: 9658
Experience: Math, science and 25 years experience in computer programming
John D and 127 other General Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
  • He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Ron

    ASE Certified Technician

    Satisfied Customers:

    21496
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
< Last | Next >
  • http://ww2.justanswer.com/uploads/FO/fordguy4u/2011-12-17_222940_HPIM1257.64x64.JPG Ron's Avatar

    Ron

    ASE Certified Technician

    Satisfied Customers:

    21496
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
  • http://ww2.justanswer.com/uploads/lyeung1/2010-07-25_032152_tn_IMG_0241.JPG Dr. Y.'s Avatar

    Dr. Y.

    Urologist

    Satisfied Customers:

    18544
    I am fellowship trained specializing in general urology and reconstructive urology.
  • http://ww2.justanswer.com/uploads/docjohn174/2008-12-13_170143_johnask.jpg John's Avatar

    John

    Home Appliance Technician

    Satisfied Customers:

    13453
    Appliance repair business owner for over 43 years.
  • http://ww2.justanswer.com/uploads/MU/multistatelaw/2011-11-27_173951_Tinaglamourshotworkglow102011.64x64.jpg Tina's Avatar

    Tina

    Lawyer

    Satisfied Customers:

    8591
    JD, BBA Over 25 years legal and business experience.
  • http://ww2.justanswer.com/uploads/dermdoc19/2010-09-30_160749_Photo_122807_015.JPG dermdoc19's Avatar

    dermdoc19

    Dermatologist

    Satisfied Customers:

    3890
    30 years practice in general and cosmetic dermatology
  • http://ww2.justanswer.com/uploads/BI/birddoctor/2012-6-22_173214_birddoctor.64x64.png Dr. Pat's Avatar

    Dr. Pat

    Bird Veterinarian

    Satisfied Customers:

    3429
    25+ years working primarily or exclusively with birds
  • http://ww2.justanswer.com/uploads/RY/rydergar/2012-6-6_192240_IMG0328.64x64.JPG Dr. Gary's Avatar

    Dr. Gary

    Cat Veterinarian

    Satisfied Customers:

    3357
    DVM, Emergency Veterinarian, BS (Physiology)