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
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!