Combining multiple criterias with countifs and has

Options
Mary Kam
Mary Kam ✭✭✭✭

I would like to simplify my formula

=COUNTIFS({WK 24 Range 1}, HAS("5:00", @cell), {WK 24 Range 2}, "") + COUNTIFS({ WK 24 Range 1}, HAS("5:15", @cell), {WK 24 Range 2}, "") + COUNTIFS(WK 24 Range 1}, HAS("5:30", @cell), {WK 24 Range 2}, "") + COUNTIFS({ WK 24 Range 1}, HAS("5:45", @cell), { WK 24 Range 2}, "")

Is there a way to get the formula to have multiple criterias 5:00, 5:15,5:30,5:45 all together.

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Mary Kam

    Please try the following formula:

    =COUNTIFS({WK 24 Range 1}, OR(HAS("5:00", @cell), HAS("5:15", @cell), HAS("5:30", @cell), HAS("5:45", @cell)), {WK 24 Range 2}, "")

    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"

  • Mary Kam
    Mary Kam ✭✭✭✭
    Answer βœ“
    Options

    Thank you so much for the prompt reply. The formula worked perfectly, and will make my work more efficient.😁😁😁

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer βœ“
    Options

    @Mary KamΒ 

    You are welcome and I will be happy to help you any time.

    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"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Mary Kam

    Please try the following formula:

    =COUNTIFS({WK 24 Range 1}, OR(HAS("5:00", @cell), HAS("5:15", @cell), HAS("5:30", @cell), HAS("5:45", @cell)), {WK 24 Range 2}, "")

    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"

  • Mary Kam
    Mary Kam ✭✭✭✭
    Answer βœ“
    Options

    Thank you so much for the prompt reply. The formula worked perfectly, and will make my work more efficient.😁😁😁

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer βœ“
    Options

    @Mary KamΒ 

    You are welcome and I will be happy to help you any time.

    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!