Filter by date in the "Sheet Summary"?
The formula I want is something that filters "Need By Date" column (Pic 1) to a specific month (Jan, Feb, Mar, etc) then count how many times Status column is "User Received" and display it in the sheet summary (Pic 2)
I've tried a million different ways, forwards, backwards, and upside down, and am about to find and sacrifice a deer in my backyard to the gods of 1s and 0s, but I'd like to find an answer here first since it'd be less to clean up. WHAT'S WRONG WITH THIS #UNPARSEABLE FORMULA??
=IF([Need By Date]1:[Need By Date]800), COUNTM(MONTH([Need By Date])2),0)
**I have had versions of the formula return "1" in a few occasions.
I'm learning, it's winter, and I have a house full of pandemic home school kids (plus a toddler), so please be kind. I am certain there are tiny errors (commas etc) in the above formula. I know basic formula structure and all of that from excel and SQL training, and am happy to receive any tips.
I hate that smartsheet is a 'relational database' yet there is no primary key-I know the primary column is a substitute, but its making me crazy to see duplicates in a "Primary" anything. Data redundancy is an issue. It seems to me that smartsheets make users look dumb and should really be more of a WYSIWYG program with this low level of database understanding with its users!
Ok. Rant over. :)
Best Answer
-
Hi @Sarah Cobb
You were close. You needed to start with COUNTIFS. Also best to just refer to the entire column as opposed to using column numbers (unless your data is static).
I would suggest that you try the following:
=COUNTIFS([Need by Date]:[Need by Date], IFERROR(MONTH(@cell), 0) = 2)
All the best,
Sandra
Answers
-
Hi @Sarah Cobb
You were close. You needed to start with COUNTIFS. Also best to just refer to the entire column as opposed to using column numbers (unless your data is static).
I would suggest that you try the following:
=COUNTIFS([Need by Date]:[Need by Date], IFERROR(MONTH(@cell), 0) = 2)
All the best,
Sandra
-
Got it!
=COUNTIFS(Status:Status, "USER RECEIVED", [Need By Date]:[Need By Date], IFERROR(MONTH(@cell), 0) = 2)
It counts the number of times "User Received" is in the Status for the month of 2 (Feb)! I love this wizardry. Thank you!
-
I have a similar problem. I am trying to sum a certain column based on a date range on the a summary report. I need to sum the agent planned total from the store scheduled date from 10/12 thru 10/26.
Thanks,
Cory
-
Hi @Cory McGhee
Try something like this:
=SUMIFS([Agent Planned Total]:[Agent Planned Total], [Scheduled Agent Arrival Date]:[Scheduled Agent Arrival Date], >=DATE(2022, 10, 12), [Scheduled Agent Arrival Date]:[Scheduled Agent Arrival Date], <=DATE(2022, 10, 26))
See: DATE Function & SUMIFS Function
If you're on a Business or Enterprise plan, another easy option would be to create a Row Report, filter by your date range, then use the Summary feature in the report. See: Summarize content to extract key information with report builder
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives