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.

Help Article Resources
Categories
Check out the Formula Handbook template!