Formula no longer working in Sheet Summary

I have a sheet with sheet summaries that were working for the last month and I recently updated with data shuttle and its no longer working. I keep getting the sum of 0 even though it was working before.

=SUMIFS([Quantity (G)]:[Quantity (G)], Material:Material, CONTAINS("200002", @cell), [Pick-up Date]:[Pick-up Date], @cell > TODAY(-90))

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Paul Newcome

Hmm... So we have actual dates in the date column, actual numbers in the sum column, and consistent data types in the id column. I wonder if it is the CONTAINS function not liking the fact that it is looking at numerical values.

Try converting that helper column we've been using into a text/number column and enter this:

=Material@row + ""

Then adjust your SUMIFS to look at this helper column for the CONTAINS function.

All comments

Paul Newcome

What happens if you enter a temporary checkbox column and use

=IF(ISNUMBER([Quantity (G)]@row), 1)

How many of those boxes are checked?

eLIZo

https://community.smartsheet.com/discussion/comment/367246#Comment_367246

All of the boxes with a quantity are checked.

Paul Newcome

Ok. What about this test...

=IF([Pick-up Date]@cel> TODAY(-90), 1)

And do any of the entries in the Material column have leading zeros?

eLIZo

I got 1 for all the 11 rows I expected the sum to come from.

and none of the entries in the material column has leading zeros.

Paul Newcome

Try converting that helper column we've been using into a text/number column and enter this:

=Material@row + ""

Then adjust your SUMIFS to look at this helper column for the CONTAINS function.

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.