# IF(COUNTIFS - Syntax help!

✭✭✭✭
edited 02/01/23

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.

• ✭✭✭

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?

• ✭✭✭✭
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.

• ✭✭✭✭✭✭

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

• ✭✭✭

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!