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

Automations 1

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!

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.