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
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!