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}

Ipshita

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

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}

Taylar LaBonte

@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

Happy to help! Have great day as well 😀

Ipshita

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