IF(AND) Statement With List of Expressions

Hello,

I'm a fairly new user to Smartsheet and trying to put together some formulas. I'm currently trying to implement a formula to summarize a column called Status if the rest of the cells in that column have a "Complete" entered in them. If there is at least one cell with anything other than "Complete", I want "Incomplete" to show in the top summarizing cell of that column. The formula I'm implementing is below:

=IF(AND(Status2:Status16 = "Complete"), "Complete", "Incomplete")

I get back a message of #INVALID OPERATION in the top summarising cell using this formula.

I see that in the Smartsheet help tab, it's suggested you list the inner AND expressions one after the other but I'd like to list them as one as they share a common desired entry ("Complete").

Any help would be appreciated!

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

Paul Newcome

Try something like this...

=IF(COUNTIFS(Status2:Status16, "Complete") = 15, "Complete", "Incomplete")

This will count how many are complete, and if that number is 15 (number of rows specified) then it will show as "Complete". Until then, it will show as "Incomplete".

If your extra rows are indented below row 1 (where I assume you are putting the formula), you can use the CHILDREN function to make things much more flexible.

=IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Incomplete")

GSheehan

Thanks for the response. My rows below are indeed indented. If possible, I'd like a way to not have to use the COUNTIFS function as I won't always have a fixed number of rows and want if possible to avoid having to change the formula every time a new row is added.

Paul Newcome

If they are indented, you can use the second formula that uses the CHILDREN function. This will automatically account for rows being added/deleted that are indented under the parent row.

GSheehan

Great thank you. I'll give it a try!

Paul Newcome

Let us know how it works!

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.