Using IsBlank formula across multiple cells
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!
Best Answer

@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
Answers

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.

Alternating

@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

This is very helpful! Thank you!

@wyandj Happy to help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!