CountIf Formula with And

Options

Hi,

I'm trying to write a formula to count the number of facilities (rows) with the following conditions:

1) Exec is Sara

2) Level is Integrated

3) Kick-off is Yes

4) MSA is Yes

5) Users is Yes

6) On Hold is not checked

7) Opted Out is not checked

Would it be easiest to do an AND formula for Count If? I keep getting an Error on my formula and can't figure it out πŸ˜‘

Made a mock-up really quick for a visual:


Best Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 02/23/23 Answer βœ“
    Options

    @Taylar LaBonte Good morning. A countif formula is inherently an "And" formula. All range-criteria pairs must be true for it to return a number. Your formula should look like this

    =countifs(Exec:Exec, "Sara", Level:Level, "Integrated", [Kick-off]:[Kick-Off], "Yes", MSA:MSA, "Yes", Users:Users,"Yes",[On Hold]:[On Hold], 0, [Opted Out]:[Opted Out], 0)

    If you are doing this formula on a different sheet, you will need to use cross sheet references, and then replace your cross sheet reference range in the proper places, so if create a cross sheet reference called "Exec" you would replace Exec:Exec in the formula with {Exec}

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Taylar LaBonte ,

    I tested out your issue and here is the solution for you :)

    =COUNTIFS(Exec@row, "Sara", Level@row, "Integrated", [Kick-off]@row, "Yes", MSA@row, "Yes", Users@row, "Yes", [On hold]@row, 1, [Opted Out]@row, 1)

    Hope this helps!

    Cheers!

    Ipshita

    Ipshita Mukherjee

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 02/23/23 Answer βœ“
    Options

    @Taylar LaBonte Good morning. A countif formula is inherently an "And" formula. All range-criteria pairs must be true for it to return a number. Your formula should look like this

    =countifs(Exec:Exec, "Sara", Level:Level, "Integrated", [Kick-off]:[Kick-Off], "Yes", MSA:MSA, "Yes", Users:Users,"Yes",[On Hold]:[On Hold], 0, [Opted Out]:[Opted Out], 0)

    If you are doing this formula on a different sheet, you will need to use cross sheet references, and then replace your cross sheet reference range in the proper places, so if create a cross sheet reference called "Exec" you would replace Exec:Exec in the formula with {Exec}

  • Taylar LaBonte
    Taylar LaBonte ✭✭✭
    Options

    @Samuel Mueller Your response was SO fast - thank you so much! The formula worked!

    I really appreciate your assistance - I've been struggling far too long trying to figure it out.

    Have a great day!

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    Happy to help! Have great day as well πŸ˜€

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Taylar LaBonte ,

    I tested out your issue and here is the solution for you :)

    =COUNTIFS(Exec@row, "Sara", Level@row, "Integrated", [Kick-off]@row, "Yes", MSA@row, "Yes", Users@row, "Yes", [On hold]@row, 1, [Opted Out]@row, 1)

    Hope this helps!

    Cheers!

    Ipshita

    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!