How can I pull in certain data from the last 30 days?

Options

I'm trying to pull in certain data points from the last 30 days to show metrics. I already have a calculation sheet that pull in the entirety of our tracker and keeps count of what we're looking at, but now we want to show it more granularly.

Essentially, I want to be able to say, "In the last 30 days, there were X number of submissions under Submission Type C."


I've looked at a ton of the questions asked and haven't found quite the one that worked just yet.

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Try something like this:

    =COUNTIFS([Submission Type]:[Submission Type], "C", [Submission Date]:[Submission Date], <=TODAY(-30))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @ZMinior,

    This should work:

    =COUNTIFS([Submission Date]:[Submission Date], >=(TODAY(-30)), [Submission Type]:[Submission Type], "C")

    Obviously for other kinds of submission, change C to the relevant type.

  • ZMinior
    ZMinior ✭✭
    Options

    Both of these solutions came back as #UNPARSEABLE. Note: I'm referencing a separate tracker sheet for this calculation. I'm sure I'm selecting the correct columns for values.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    It sounds like the cross sheet reference might not be set up correctly.

    If you want to use this formula

    =COUNTIFS([Submission Date]:[Submission Date], >=(TODAY(-30)), [Submission Type]:[Submission Type], "C")

    on a different sheet to the one that holds the columns Submission Date and Submission Type

    Then you need to replace

    [Submission Date]:[Submission Date]

    and

    [Submission Type]:[Submission Type]

    with the cross sheet reference which will be something like:

    {Date}

    and

    {Type}

    The text with the {} will be whatever you named the range when you selected the entire column to create the cross sheet reference

    The formula is then

    =COUNTIFS({Date}, >=(TODAY(-30)), {Type}, "C")

    Note, this is not

    =COUNTIFS({Date}:{Date}, >=(TODAY(-30)), {Type}:{Type}, "C")

    which is a common issue.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    @ZMinior did those formula work for you in the end?

  • ZMinior
    ZMinior ✭✭
    Options

    Unfortunately they did not. I'm not sure what I'm missing. I keep getting an error.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @ZMinior

    If the formulas are giving you trouble, I would suggest using a Report to pull the same data. You can Filter by date, then Group by Submission type and use Summary to create the calculations. Here's a free webinar showing how to do this: Redesigned Reports with Grouping and Summary Functions

    If this hasn't helped, it would be useful to see exactly what formula you've tried and what error you're getting.

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!