Quick SUMIFS question - Additional Criteria is Date

Dan B.

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.

Dan B.

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.

Marcela

Hi @Dan B.

Thank you very much for sharing your solution!

Cheers,

Marcela!