Filter by date in the "Sheet Summary"?

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 ✓

    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,