Formula no longer working in Sheet Summary

eLIZo

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

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

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

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.