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.

Find more posts tagged with

Accepted answers

All comments

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

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.

Chris Shifflett

=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

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.