IF, AND, OR (all 3) (formula) Help

Nicole Hodges

Hello!

I am trying to create a formula to count if any kind of combination of the below is met:

=IF(AND([WU Enrollment Complete]:[WU Enrollment Complete], 1, [WU Pending]:[WU Pending], 1, ([Email 1]:[Email 1], 1, [Call 1]:[Call 1], 1, [Call 2]:[Call 2], 1, [Assigned to]:[Assigned to], "MyLe Nguyen") 1,0)

It seems like the IF/AND is not allowed. Any help here?

I can't use countifs because there are cases where all the conditions are met and I would hate to double, triple, etc count.

I need the "Assigned To" to be 100% met, but the others could be one met, two, or all, but again I only want to count once. See below:

FIXED (NEED): "Assigned To" = MyLe Nguyen

ALL 3 conditions must be met: Email 1 checked, Call 1 checked, Call 2 checked

either, or, and:

- WU Enrollment Complete checked

- WU Pending Checked

 

 

Thank you!

Paul Newcome

Is this formula going to be in one location while looking at entire columns, or is it a formula you will be putting in each row to check whether those conditions have been met or not?

Nicole Hodges

Hey Paul - thanks for your response!

It will be in one location while looking at entire columns. 

Paul Newcome

Ok. Do you want an actual count of how many times ALL conditions are met, or just a checkbox saying that there is at least one row that meets the criteria? Also... Is your contact column set up as allowing multi-contacts?

Nicole Hodges

No checkbox - I want it to count "1" if any (or two, three, or all) conditions are met. I want a total.

HAS to meet the "Assigned to" criteria. In the example, needs to be "Assigned to" MyLe Nguyen. 

COUNT "1" if meets any or all below criteria (either, or, and):

- "WU Enrollment Complete" is CHECKED

- "WU Pending" is CHECKED

- ALL 3 of "Email 1", "Call 1", and "Call 2" are CHECKED

Paul Newcome

Ok. So you want to count the total number of MyLe Nguyen's rows that have complete checked, pending checked, or all three of email, call 1 and call 2 checked which would be

 

Complete

 

OR

 

Pending

 

OR

 

All 3 (email, call 1, call 2)

 

How is your Assigned to column set up? Is it a contact type column or a text type column? If it is a contact type column; is it single contact or multi-contact?

 

Unfortunately you can't use OR within a COUNTIFS statement, so you have to use a COUNTIFS for each set of the OR criteria and add them all together.

 

My suggestion would be to add a checkbox column (we'll call it "Conditions Met?" for this example) and put the following formula in to check the box if any of your conditions are met. You can then use a COUNTIFS to count the boxes that are checked and include the name there.

 

=IF(OR([WU Enrollment Complete]@row = 1, [WU Pending]@row = 1, AND([Email 1]@row = 1, [Call 1]@row = 1, [Call 2]@row = 1)), 1)

 

You would then use the following formula to count how many times that box is checked for the person:

 

=COUNTIFS([Assigned to]:[Assigned to], "MyLe Nguyen", [Conditions Met?]:[Conditions Met?], @cell = 1)

 

Do you have a list of names that you will be putting this formula next to like a master list where each person is only listed once?

Nicole Hodges

The Assigned to column is one contact.

Hm, I like that idea - trying to avoid the extra columns though! 

There are about 10 people that each have about 100 rows assigned to them.

Paul Newcome

I was actually mistaken. The OR function CAN be used within a COUNTIFS. It is the AND function that throws it off when trying to apply it the way you need to.

 

It's only one extra column, and you can Hide that to keep the sheet looking clean.

 

If you have a separate area with each person's name listed only once (see example below, column names in BOLD) you could use the following:

 

Person           Count

John                Formula

Mike                Formula

Jane                Formula

Mary               Formula

 

In the Count column where it says "Formula", you would put...

 

=COUNTIFS(=COUNTIFS([Assigned to]:[Assigned to], Person@row, [Conditions Met?]:[Conditions Met?], @cell = 1)

 

This would look at the name in the Person column for whatever row your formula is in and automatically plug that into your criteria in the COUNTIFS. That would save you from having to edit the formula multiple times to change the name.