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

Screen Shot 2021-02-17 at 6.47.06 PM.png

Screen Shot 2021-02-17 at 6.44.53 PM.png

Find more posts tagged with

Accepted answers

Sandra Guzman

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

All comments

Sandra Guzman

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

Sarah Cobb

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!

Cory McGhee

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

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.