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

Find more posts tagged with

Formulas

Legal

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Attaching a Message Sent to Vendor Acknowledging Contract
I've set up a Smartsheet that sends an approval request to a vendor to accept the terms of employment (outlined in a PDF generated with information from the sheet). They are not required to sign the document, just click Accept or Reject. While the contract (PDF) is attached to the row, and while I would have a date in the…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…