# CountIf Formula with And

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

• Overachievers
edited 02/23/23 Answer ✓

@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}

• ✭✭✭✭✭✭
Answer ✓

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

• Overachievers
edited 02/23/23 Answer ✓

@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}

• @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!

• Overachievers

Happy to help! Have great day as well 😀

• ✭✭✭✭✭✭
Answer ✓

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!