Multiple countifs

Options

Hi,

I tried using =COUNTIFS(1st criteria range, 1st criteria, 2nd criteria range, 2nd criteria,3rd criteria range, 3rd criteria).

It is giving "0" in the result, can anyone help on this?

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/20/20 Answer ✓
    Options

    Hi @Prajna J

    Hope you are fine, please find in the following sample to explain your question

    in this sample, i need to count how many (A,Bus,Red)

    the formula =COUNTIFS([criteria1]:[criteria1], "A", [criteria2]:[criteria2], "Bus", [criteria3]:[criteria3], "Red")

    and the result = 3

    please try it and read the following article it will help you to understand the use of COUNTIFS


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 12/20/20 Answer ✓
    Options

    Hi Prajna

    If you've set up your formula using @Bassam.M Khalil excellent advice and you're still returning a zero, eliminate all but one term in your COUNTIFS. Then systematically add the terms in one by one to try to pinpoint which term(s) is giving you the problem. Note: You don't have to change to a COUNTIF (singular) if you edit down to only one criteria - the great thing of COUNTIFS (plural) is it can be used all the time regardless of the number of criteria.

    If you still experience problems, provide a screenshot (omitting any sensitive info) and the formula and the community can help you troubleshoot.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/20/20 Answer ✓
    Options

    Hi @Prajna J

    Hope you are fine, please find in the following sample to explain your question

    in this sample, i need to count how many (A,Bus,Red)

    the formula =COUNTIFS([criteria1]:[criteria1], "A", [criteria2]:[criteria2], "Bus", [criteria3]:[criteria3], "Red")

    and the result = 3

    please try it and read the following article it will help you to understand the use of COUNTIFS


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 12/20/20 Answer ✓
    Options

    Hi Prajna

    If you've set up your formula using @Bassam.M Khalil excellent advice and you're still returning a zero, eliminate all but one term in your COUNTIFS. Then systematically add the terms in one by one to try to pinpoint which term(s) is giving you the problem. Note: You don't have to change to a COUNTIF (singular) if you edit down to only one criteria - the great thing of COUNTIFS (plural) is it can be used all the time regardless of the number of criteria.

    If you still experience problems, provide a screenshot (omitting any sensitive info) and the formula and the community can help you troubleshoot.

  • Mindfull
    Mindfull ✭✭✭✭✭
    Options

    Thanks a lot!!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @KDM 

    Greate advice

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!