IF(COUNTIFS - Syntax help!

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
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!