CountIf Formula with And

Taylar LaBonte

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:

Samuel Mueller

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