Sum if of Current Week +/- 1

Karrie Davis

I have a formula that will give me the current week capacity:

=SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY())), {Capacity}).

 

However I'm not sure of the formula to capacity for the next week or the past week capacity

=SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY()) +7), {Capacity}).

I would greatly appreciate some assistance.

 

Thanks

Andrée Starå

Hi Karrie,

Try this.

=SUMIF({Week Number}, @cell = (WEEKNUMBER(TODAY()))-1, {Capacity})

=SUMIF({Week Number}, @cell = (WEEKNUMBER(TODAY()))+1, {Capacity})

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Karrie Davis

Thank you another quick question, I want to add another criteria.

 

=SUMIFS({Week Number}, @cell = (WEEKNUMBER(TODAY())), {Capacity}, {Work Type}, @cell= "Budget")

 

Thanks

Andrée Starå

Happy to help!

Your correct that you'll need to use a SUMIFS formula but that formula is structured differently.

More info: https://help.smartsheet.com/function/sumifs

Hope that helps!

Karrie Davis

I thought i  wrote the formula correctly but I still can't get it to work. I want the sum of capacity if it is the current week and work status is in progress. 

 

=SUMIFS({Capacity}, {Week Number}, @cell = (WEEKNUMBER(TODAY())), {Caitlin status}, "In Progress")

Karrie Davis

Thanks I figured it out

 

Andrée Starå

Glad to hear that you solved it!