SUMIFS a number is within a range

Tori Heath

Hi Community!

I am trying to come up with a formula that will sum a column titled 'Hours/Week' based on if a specific person is 'Assigned To' the project and a number (the Week # being evaluated) is greater than or equal to and less than or equal to two other numbers (the project's Start and End Week numbers). This is the formula that I've come up with that is returning Incorrect Argument Set:

=SUMIFS({Hours/Week}, {Greenbacker 2.0 Project Sequencing Assignee}, "Tori Heath", [Week #]@row, >={Start Week Number}, [Week #]@row, <={End Week Number})

A huge thank you in advance for any tips you may have!!

BullandKhmer

=SUMIFS({Hours/Week}, {Greenbacker 2.0 Project Sequencing Assignee}, "Tori Heath", [Week #]@row, >={Start Week Number}, [Week #]@row, <={End Week Number})

You need to switch your sequence... the criterion goes last...

=SUMIFS({Hours/Week}, {Greenbacker 2.0 Project Sequencing Assignee}, "Tori Heath",{Start Week Number},<=[Week #]@row,{End Week Number},>=[Week #]@row)

Does this work?

Tori Heath

Hmm, wouldn't that be saying "if the Start Week # is less than or = to the Week # being evaluated and the End Week # is greater than or = to the Week # being evaluated" when really I want to be saying "if the Week # being evaluated is within or = to the Start and End weeks (greater than or = to Start Week or less than or = to End Week).

In your suggestion the Start and End Week need to be the exact same number as the Week # in order for it to count it. I am looking for something that will identify if the Week # is within the Start and End Week of a project. Let me know if I can provide any additional clarity? Thanks!!

Tori Heath

Would love to hear your thoughts BullandKhmer or anyone else out there who may have ideas. Thanks very much in advance!

Tori Heath

Is it possible it is something like: =IF(AND([Week #]1 >= {Start Week Number}, [Week #]1 <= {End Week Number}), SUMIF({Greenbacker 2.0 Project Sequencing Assignee}, "Tori Heath", {Hours per Week}), "")