If Contains formula

I need to compare a column that contains only week ending dates to a reference sheet that has a date column that could be any date. And also look for the word 'yes' in a range of words. And also look for the word 'weekly' from a reference sheet. All this to give a return of 'Completed' if true, and 'Incomplete' if false. A 3rd return value of 'Receiving not operating' if all the range is 'No'.

In the screenshot, for Week Ending 10/05/25, the Moisture Reader Weekly column should return a Completed value because the RCV equipment was operating during the week ('Yes"), and the word 'Weekly' was chosen from a column in my reference sheet.

This is a formula I got to work with daily date comparisons, but it is the range of dates that is causing me issues.

Find more posts tagged with

Accepted answers

All comments

Philip Robbins

@Alice L, I've not seen an IF(COUNTIFS( formula used in this way, but assuming the one you shared is working as expected, you likely just need an extra condition in the second COUNTIFS to allow you to handle the date range. Something like this should be close:

=IF(COUNTIFS([Was RCV Equipment Operating]@row,"No"),"Receving Not Operating-"+IF(COUNTIFS({Daily/Weekly Moisture Reader Cleaning Date},@cell <= Date@row,{Daily/Weekly Moisture Reader Cleaning Date},@cell > Date@row-7,{Daily/Weekly Moisture Reader Cleaning}, "Weekly"),"Daily),"Completed","Incomplete")

Alicia L

I get an incorrect argument set error. Is there another formula you would use that isn't if/countifs?

The Week Ending column should be in the formula. One of the arguments is to look for dates in the reference sheet that fall with the week of the Week Ending date.

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.