Filter by date in the "Sheet Summary"?

Options
Sarah Cobb
Sarah Cobb ✭✭✭
edited 02/18/21 in Smartsheet Basics

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

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Sarah Cobb ✭✭✭
    Options

    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
    Options

    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.
    Genevieve P. Employee
    edited 11/18/22
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now