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

ZMinior · 11/02/23

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.

Nic Larsen · 11/02/23

Try something like this:

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

Nick Korna · 11/02/23

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 · 11/02/23

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 · 11/03/23

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 · 11/06/23

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

ZMinior · 01/12/24

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. · 01/15/24

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

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

Answers

Help Article Resources

Categories