# 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.

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!

Tags:

• ✭✭✭✭✭

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 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 :)

• ✭✭✭✭✭

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.

• ✭✭✭✭✭
edited 08/12/20

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