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.
Answers
-
Try something like this:
=COUNTIFS([Submission Type]:[Submission Type], "C", [Submission Date]:[Submission Date], <=TODAY(-30))
-
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.
-
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.
-
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.
-
@ZMinior did those formula work for you in the end?
-
Unfortunately they did not. I'm not sure what I'm missing. I keep getting an error.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!