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

Nicole Hodges
Nicole Hodges ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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!

Comments

  • Paul Newcome
    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
    Nicole Hodges ✭✭✭✭✭✭

    Hey Paul - thanks for your response!

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

  • Paul Newcome
    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
    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
    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
    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
    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.