# Combining IF functions to read multiple columns and create a flag

Options

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!

Tags:

• ✭✭✭✭✭
Options

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

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• Options

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)), "")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!