# 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.

• ✭✭✭✭✭✭
Options

Try something like this:

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

• ✭✭✭✭✭✭
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.

• ✭✭
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.

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

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!