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

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.

Find more posts tagged with

Accepted answers

All comments

Nic Larsen

Try something like this:

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

Nick Korna

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

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

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

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

ZMinior

https://community.smartsheet.com/discussion/comment/403218#Comment_403218

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

Genevieve P.

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

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.