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.

Tags:

Answers

  • Dan B.
    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
    Marcela Employee

    Hi @Dan B.

    Thank you very much for sharing your solution!

    Cheers,

    Marcela!

    Join us at Smartsheet ENGAGE 2024🎉

    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!