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), [Pickup Date]:[Pickup Date], @cell > TODAY(90))
Best Answer

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.
Answers

What happens if you enter a temporary checkbox column and use
=IF(ISNUMBER([Quantity (G)]@row), 1)
How many of those boxes are checked?

All of the boxes with a quantity are checked.

Ok. What about this test...
=IF([Pickup Date]@cel> TODAY(90), 1)
And do any of the entries in the Material column have leading zeros?

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.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!