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!