Quick SUMIFS question - Additional Criteria is Date
Hello,
I have a quick question for the formula gurus. I have two sheets, a Roster and Data sheet. My roster sheet is where I want to show the data and the Data sheet is just a collection form. My goal is to display a point total that only pulls points from the last two weeks (14 days).
So this is my data sheet. As we can see, Doug got a point on 11/14. I want to run a sumifs to not count that entry since it's past 2 weeks.
Here is what my roster looks like:
So right now, I got Doug at 3 points, but I want him at 2 because one of his points was past the 2 week limit.
Here is my current formula in the "Last 2 Weeks" column:
=SUMIFS({Point Value}, {Name}, Name@row)
Now, what I keep struggling with is adding that 2 week limiter to the formula.
So I'm looking at what smart sheet says about the formula.
SUMIFS( range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
So what I'm thinking is =SUMIFS({Point Value}, {Name}, name@row, {Point Value}, {Date}, TODAY( ) - 14)
But I'm getting #UNPARSABLE so I feel like I'm missing something here.
Any help would be appreciated.
Answers
-
I worked with it some more and figured out what I did wrong. I'll explain in case this will help someone in the future.
I basically misread the formula.
SUMIFS( range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
The key is that I thought I had to list the range multiple times. See below:
=SUMIFS({Point Value}, {Name}, name@row, {Point Value}, {Date}, TODAY( ) - 14)
When I took out the second reference, I started to get a formula that works, but it wasn't showing the right answer I was looking for, so then I just threw this in there because I've seen other formulas use it (@cell)
=SUMIFS({Point Value}, {Name}, name@row, {Date}, @cell >=TODAY( ) - 14)
And this is how I got it to work.
-
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.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!