#BLOCKED Error with COUNTIFS when Range columns are populated via equations

Hi,

I am trying to calculate the amount of "Used Inventory" by counting the number of times "Block Date" appears between "DATE 1" and "DATE 9" but am getting the #BLOCKED error.

Columns "DATE 1" through "DATE 9" are populated by equations based on "CheckIn Date" and "CheckOut Date."

Below, you can see the columns in use and the equation.

When I change the formula to specify the rows for [DATE 1] and [DATE 9], as shown here…

The formula works.

I am trying to turn this sheet into a Template, where "Used Inventory" has a column formula, rather than having to have my team use the row #s.

Any guidance would be greatly appreciated.

Find more posts tagged with

Templates

Sheets

Hospitality

Formulas

columns

Accepted answers

All comments

bisaacs

Hey @Emily Desjardins,

A #BLOCKED error happens when it's trying to pull data that it can't parse/read, so there's something in your data range that's not parsed correctly. When you change the range to only cover 9-50, then that means there's something outside that data range that's not formatted/entered correctly.

Hope this helps!

Emily Desjardins

Hello @bisaacs,

Thank you. I'd already determined that. However, as the equations in columns DATE 1 to DATE 9 all work, I was hoping for some guidance in how to find the formatting/entry error. What should I be looking for, etc.?

bisaacs

Hey @Emily Desjardins,

Hmmm. I relooked at your sheet and are these parent/child rows? If so, I wonder if that's causing the error when you try to apply it to the whole column? Otherwise I'd maybe change the range to only cover 1 column at a time, and see if you get that error for every column or just specific ones. If it's just one or a handful, then I'd reapply the date formatting to confirm they match.

Emily Desjardins

Hello @bisaacs,

They are not Parent/Child rows. Changing the equation to look at one column at a time also yields #BLOCKED.

If there are more suggestions, I would appreciate examples of new equations along with them. It is difficult for me to transpose written text into action (equations) and I learn better by having visual demonstrations. :)

Emily Desjardins

Good morning @bisaacs and the greater Smartsheet community!

I was able to solve my issue and get the equation to work.

bisaacs

Hey @Emily Desjardins,

Glad to hear! How did you end up getting it to work?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.