IFERROR Formula Not Working

Good day, team.

I have a formula that works as intended, which takes the latest row of children data and inputs that as the value. That formula is:

=(INDEX(CHILDREN(), COUNT(CHILDREN())))

and produces the following results:

However, if there is no data in the children cells it gives me an "Invalid Column Value" as shown below.

I thought I could turn this into an IFERROR statement simply by changing the formula to:

=IFERROR(INDEX(CHILDREN(),COUNT(CHILDREN()))),"0") and have it display a zero if there was no data, but I get an error still.

Any suggestions?

Thank you kindly in advance for your time.

-Brian

Find more posts tagged with

Accepted answers

KPH

The error is caused by the COUNT being 0 and there is no row 0 so the INDEX can't return a value. You could add an IF to the start to check the COUNT is greater than 0. Something like this:

=IF(COUNT(CHILDREN()) > 0, INDEX(CHILDREN(), COUNT(CHILDREN())), "0")

All comments

Paul Newcome

Try removing one of the closing parenthesis from that group after the second CHILDREN function. There should only be 3 there, but you have 4.

KPH

The error is caused by the COUNT being 0 and there is no row 0 so the INDEX can't return a value. You could add an IF to the start to check the COUNT is greater than 0. Something like this:

=IF(COUNT(CHILDREN()) > 0, INDEX(CHILDREN(), COUNT(CHILDREN())), "0")

Paul Newcome

@KPH The IFERROR mentioned should have taken care of that issue. The problem with the final formula in the original post (the one with the IFERROR) is the extra closing parenthesis tucked in after the second CHILDREN function.

Disregard.

Brian Wilson DC

https://community.smartsheet.com/discussion/comment/490048#Comment_490048

@Paul Newcomel: Unfortunately, this did not resolve the issue. I may have typed an extra parentheses in the above, but I had it correct on my sheet. I tried changing the value at the end of the equation to, which it was showing as the "Value if False" on the tool tip:

),0)

),"0")

),=0)

),"No Data")

and none of them worked (and I had matching opening and closing parenthetical statements).

The answer suppled by @KPH did resolve the issue.

Thank you both for your time and effort!

Paul Newcome

Interesting that the IFERROR didn't correct the issue. Thanks for the update.

KPH

Glad you got it sorted @Brian Wilson DC

@Paul Newcome , I don't think the IFERROR at the start would solve the problem caused by the 0 in the row index. With the parenthesis correct, this would still be an Invalid Column Value. I felt the cleanest solution was to only evaluate the INDEX if there were child rows.

Brian Wilson DC

@Paul Newcome Yes, it was a complete surprise to me that it didn't work, as well. I feel like I have a really strong fundamental understanding of the formulas and I couldn't for the life of me figure out why this wasn't working.

As an aside, I appreciate all of your help over the past year. I feel like I've gleaned a lot of really useful information from the posts you have made to other commenters.

Paul Newcome

@KPH I would have thought the IFERROR would work the same way it does when there is no match. There is nothing to index from. I've also used it frequently in similar ways to this such as pulling in the last entry in a column or row using a COUNTIFS. Even then I'd use the IFERROR to output a blank when the COUNTIFS output a zero.

I have even used it with the CHILDREN function, and the IFERROR worked as expected. Not really clear why it didn't work in this instance.

Paul Newcome

https://community.smartsheet.com/discussion/comment/490054#Comment_490054

I'm glad some of my posts have been helpful. That's the goal after all...

KPH

Hi @Paul Newcome

I think it is something special/unique/weird with 0. The count is not returning an error like a no match would, but 0 isn't a valid entry, so does cause an error. One of those corner cases that doesn't make sense. There are a few - kind of kooky - keeps it interesting!

Paul Newcome

https://community.smartsheet.com/discussion/comment/490058#Comment_490058

Right, but I've had the IFERROR fix this very challenge in the past including using the CHILDREN references. This one is weird for sure.

I know there are some errors that it can't handle, but even that error seems a little unexpected given the situation. That one usually comes from trying to do something like put a date in a text/number column (if I remember correctly).

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.