Using IsBlank formula across multiple cells

wyandj

I have a sheet that collects ordering information from a Form, which could have 1 - 10 items. Each row tracks 1 order form and I would like to have a column that reads "Completed" once all the items on that order form have arrived. Each item has a description column and received column. As items are received we update the received column to "yes"

I believe IsBlank will be the best for this situation but I'm not sure how to write the formula. The challenge I am having is there are 10 description columns and 10 received columns in 1 row. I need the formula to look at the description column and if that is filled than include the received column. Once all of the received columns are filled "yes" than the order would be "Completed".

Example: Some orders are 1 item and some are 10 so the formula has to be able to calculate if there are only 2 item description columns filled once the 2 associated received columns are "yes" than the order is "completed"

I hope this makes sense, any help would greatly be appreciated!

Heather Duff

@wyandj Try something like this:

Because your columns are alternating, you'll want to add two helper columns. My first helper column ("DesCount") has the following formula:

=COUNT([Description1]@row, [Description2]@row, [Description3]@row)

My second helper (RecCount) column has the following formula:

=COUNTIF([Description1]@row:[Received3]@row, "Yes")

For the RecCount formula, it is safe to reference all of the rows (rather than each individual Received column) because I wouldn't anticipate any of the descriptions to just be "Yes."

Of course, you'll want to expand the formulas to include all 10 sets of columns, rather than just three. I then used the following formula in my Completed column:

=IF(DesCount@row = RecCount@row, "Completed", "")

Hope this helps! Let me know if it works.

Best,

Heather

Heather Duff

Hi @wyandj ,

Are all of your description columns grouped together, and all of your received columns grouped together? Or are they alternating (Description1, Received1, Description2, Received2, etc.)? This will change how you set your formulas up.

wyandj

Alternating

Heather Duff

@wyandj Try something like this:

Because your columns are alternating, you'll want to add two helper columns. My first helper column ("DesCount") has the following formula:

=COUNT([Description1]@row, [Description2]@row, [Description3]@row)

My second helper (RecCount) column has the following formula:

=COUNTIF([Description1]@row:[Received3]@row, "Yes")

For the RecCount formula, it is safe to reference all of the rows (rather than each individual Received column) because I wouldn't anticipate any of the descriptions to just be "Yes."

Of course, you'll want to expand the formulas to include all 10 sets of columns, rather than just three. I then used the following formula in my Completed column:

=IF(DesCount@row = RecCount@row, "Completed", "")

Hope this helps! Let me know if it works.

Best,

Heather

wyandj

This is very helpful! Thank you!

Heather Duff

@wyandj Happy to help!