Check off completed if items collected, with different number of items needed to be collected.

Hi,

I'm trying to make a list of people and items I need to collect for them. Sometimes I need to collect 1 item, sometimes 2 or 3. The last column I want to write a formula that will check the box if everything is collected for that person.

Capture f.JPG

Since every situation is different and I need to collect a different number of items, i believe the formula needs to read something like, "It item 1 is not blank, and collected 1 is checked, OR if item 1 is blank, AND if item 2 is not blank and collected 2 is checked OR if item 2 is blank AND if item 3 is not blank and collected 3 is checked OR item 3 is blank, Check off this box, otherwise leave blank".

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

David Joyeuse

Hi Ricky,

Is the number of items you need to collect limited to 3 or can it be more than 3 on some occasions?

Until then I would ride with something like in your completed column:

=IF(COUNTIFS([Item Needed 1]@row,NOT(ISBLANK()),[Item Needed 2]@row,NOT(ISBLANK()),[Item Needed 3]@row,NOT(ISBLANK()))=COUNTIFS([Received 1]@row,1,[Received 2]@row,1,[Received 3]@row,1),1,0)

If the completed column is a checkbox, it will be checked each time the numbers of item needed exists and the checkbox are equals.

Please notes that it do check the box if for example:

Item Needed 1 is not blank. Received 1 is not checked, but Received 2 (or 3) is checked. Which may only happens if you check the wrong box.

Hope it helped, let me know if you need further assistance on this :)

Automations 1

Hi, @DavidJoyeuse

The number of items I need to collect is limited to three. The completed column is a checkbox, yet I'm getting #incorrect argument when I pasted the formula into the completed column.🙁

I changed the NOT(ISBLANK()) to <>"" so now my formula reads as follows:

=IF(COUNTIFS([Item Needed 1]@row, <>"", [Item Needed 2]@row, <>"", [Item Needed 3]@row, <>"") = COUNTIFS([Received 1]@row, 1, [Received 2]@row, 1, [Received 3]@row, 1), 1, 0)

This works but ONLY when all three items needed are filled in. If I just have two filled in, it doesn't work. Also, when I add a row and the formula autofills down, the checkbox get's checked.

David Joyeuse

Hi @Rivky Emert

Sorry but I got this completly wrong the first time. Shouldn't answer stuff late at nite :P

It should read like this:

=IF((COUNTIF([Received 1]@row,1)+COUNTIF([Received 2]@row,1)+COUNTIF([Received 3]@row,1))=COUNT([Item Needed 1]@row, [Item Needed 2]@row, [Item Needed 3]@row),1,0)

That will be working fine.

COUNT is only counting cells that are not blank.

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.