Formula no longer working in Sheet Summary

eLIZo · 03/14/23

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))

Paul Newcome · 03/16/23

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.

Paul Newcome · 03/14/23

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 · 03/14/23

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

All of the boxes with a quantity are checked.

Paul Newcome · 03/15/23

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 · 03/15/23

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 · 03/16/23

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.

Formula no longer working in Sheet Summary

Best Answer

Answers

Help Article Resources

Categories