IF(COUNTIFS - Syntax help!

mistone
mistone ✭✭✭✭
edited 02/01/23 in Formulas and Functions

I'm trying to build a logic formula on Sheet A that returns a statement based on 3 criteria - Checkbox columns on 2 sheets. checkbox columns W & X are on sheet B and checkbox column Z is on Sheet C.

I've tried IF AND, IF AND OR, and now I'm onto if IF(COUNTIFS. Here is what I'm trying to do (Checkbox checked = 1 unchecked box = 0):

IF W =1 X = 0 Z = 0 THEN "ORDER"

IF W = 1 X = 1 Z = 1 THEN "ALL"

IF W = 0 X = 1 Z = 1 THEN "CALL & EMAIL"

IF W = 0 X = 0 Z = 1 THEN "EMAIL"

IF IF W =1 X = 1 Z = 0 THEN "ORDER & EMAIL"

IF W = 1 X = 0 Z = 1 THEN "ORDER & CALL"

OTHERWISE "NONE"

Here is the formula I've started...

=IF(COUNTIFS({3.0 IR Sheet - Panera Col}, 1, {3.0 IR Sheet - Itinerary Col}, Itinerary@row, {3.0 IR Sheet - Logi Email Col}, 1, {3.0 MLR Sheet - Pre-Dep Col}, 1, {3.0 MLR Sheet - MEET Loc Col}, [Meeting location]@row, 1) = 1, "ALL", "NONE")

If you choose to accept the challenge, then I thank you in advance.

Answers

  • DKazatsky
    DKazatsky ✭✭✭

    @mistone

    Are W, X, and Z only being used in one row or entire column? If entire column, what is the key that matches the rows across sheets?

  • mistone
    mistone ✭✭✭✭
    edited 02/01/23

    W, X, Z are being used by the entire column. I started with very long nested IF, IF AND, IF AND OR with INDEX MATCH to with little success. I've been told using IF(COUNTIFS is a better and easier way to accomplish this.


    The IF(COUNTIFS( formula is referencing Column W X and Z using @row on Sheet A to match to the row to the columns.

  • =IF(AND(B:B=1, C:C=0, D:D=0), "ORDER",

    IF(AND(B:B=1, C:C=1, D:D=1), "ALL",

    IF(AND(B:B=0, C:C=1, D:D=1), "CALL & EMAIL",

    IF(AND(B:B=0, C:C=0, D:D=1), "EMAIL",

    IF(AND(B:B=1, C:C=1, D:D=0), "ORDER & EMAIL",

    IF(AND(B:B=1, C:C=0, D:D=1), "ORDER & CALL",

    "NONE"))))))

    columns W, X, and Z are referenced in this formula as B:B, C:C, and D:D respectively. The "@row" operator is not needed as the formula is written as a column formula, which will apply to all cells in the column where the formula is placed.

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    I feel like I'm missing why the counting is necessary if you are wanting those phrases? If you are really wanting to restrict helper columns a complex nested IF would likely be best, but I would suggest a column for each status (hidden) and then one to report the complete picture.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • DKazatsky
    DKazatsky ✭✭✭

    @mistone

    Try this:

    =IF(COUNTIFS({W Range}, 1, {X Range}, 1, {Z Range}, 1, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "All", IF(COUNTIFS({W Range}, 1, {X Range}, 0, {Z Range}, 0, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "Order", IF(COUNTIFS({W Range}, 0, {X Range}, 1, {Z Range}, 1, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "Call & Email", IF(COUNTIFS({W Range}, 0, {X Range}, 0, {Z Range}, 1, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "Email", IF(COUNTIFS({W Range}, 1, {X Range}, 1, {Z Range}, 0, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "Order & Email", IF(COUNTIFS({W Range}, 1, {X Range}, 0, {Z Range}, 1, {Sheet 3 Meeting Location}, [Meeting Location]@row, {Sheet 2 Itinerary}, Itinerary@row), "Order & Call", "None"))))))

    You will need to change the cross sheet references as applicable.

    Hope this helps,

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!