Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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))
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([Pick-up 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
