Combining IF functions to read multiple columns and create a flag

Hi all,

I am working with the attached sheet trying to create a few flags to appear when certain information is present. The 3 flag columns are '2 Weeks - No Data', '2 Weeks - No Check Ins', '2 Weeks - No Paired Check Ins' and I have formulas within each reading the 'Check In Type' and 'Last Wk Check In' columns. Below are the formulas I am using for each column and what I am hoping to acheive.

Formula for '2 Weeks No Data': =IFERROR(IF([Check In Type]@row = <>"", 0, IF([Last Wk Check In]@row = <>"", 0, 1)), "") --> The goal is to have a flag appear when there is NO data in the Check In Type or Last Wk Check in column for a given row

Formula for '2 Weeks - No Check Ins': =IFERROR(IF([Check In Type]@row = "None", 0, IF([Last Wk Check In]@row = "None", 0, 1)), "") --> Goal is to have a flag up appear when "None" appears in both columns for a given row

Formula for '2 Weeks - No Paired Check Ins': =IFERROR(IF([Check In Type]@row = "Mentor", 0, IF([Last Wk Check In]@row = "Mentor", 0, 1)), "") --> Goal is to have a flag up appear when anything but "Mentor" appears in both columns for a given row (ex: None, Sub)

Thanks for the help and let me know if I can provide additional context!

Screen Shot 2022-07-06 at 9.26.37 AM.png

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Brent Wilson

I am hoping I understand your ask..

You can add the IFERROR if you wish around these

2 Weeks No Data

=IF(OR(ISBLANK([Check In Type]@row),ISBLANK([Last Wk Check In]@row), 1, 0)

2 Weeks - No Check Ins

=IF(OR([Check In Type]@row) = "None" ,[Last Wk Check In]@row = "None"), 1, 0)

2 Weeks - No Paired Check Ins

=IF(AND([Check In Type]@row) = "Mentor" ,[Last Wk Check In]@row = "Mentor"), 0, 1)

I think I understood on the last statement the only way to have it show "flag down" would be to Mentor in both check ins

Kishan Naik RA

Thanks for the help Brent! I think I am really close, however I am struggling to get the formulas to match the '2 weeks in a row' that I am trying to get. For example in the screenshot below, the two rows that are flagged with '2 weeks no data' show a flag but there is only one week of 'No Data' whereas I only want a flag to appear if 'No Data' appears twice in a row.

Here is the formula I am using, I feel like I may be missing an AND function since I need both columns to have the same data for a flag to occur --> =IFERROR(IF([Check In Type]@row = "No Data", 1, IF([Last Wk Check In]@row = "No Data", 1, 0)), "")

Screen Shot 2022-07-11 at 5.02.47 PM.png

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.