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!