Capacity Planning Formula

Rachel Mickelson

Hello!

I'm looking for a way to track the amount of hours assigned to a person by week and by month. I’ve gotten a formula to count total hours for a specific person, but I’m stuck on how to get to the next level with supporting my team with capacity planning. Hoping for a different/beefier formula or a way to build on what I’ve already got in place (i.e. an additional formula). I’m looking into using Resource Management again but historically it didn’t have the best view/solution for this.

Jgorsich

If you can share your current formula, it would likely be helpful.

That being said - presumably your current formula ALREADY includes some filtering based on employee name, you just need to add ADDITIONAL filtering (if you are using a Collect() function, just keep adding criteria columns and criteria) for various date ranges. Note that you can look for the dates inside of a range by having one criteria that looks for entries on or after the first date and a second criteria that looks for entries on or before the last date - the net result will be only those entries that fall between the two dates.

Rachel Mickelson

Here is my current formula: =SUMIFS([Est. work hours]:[Est. work hours], [Assigned to]:[Assigned to], "Jenny")

I have not used a Collect() function/formula so would appreciate any input on how to incorporate this into the above formula.

Jgorsich

Okay, cool - so, sumifs allows multiple criteria to be added. So, if you wanted a table of just work assigned to Jenny for the week of 8/10/25 through 8/16/25, you could do it like this:

=SUMIFS([Est. work hours]:[Est. work hours], [Assigned to]:[Assigned to], "Jenny",[Start Date]:[Start Date],>DATE(2025, 8, 9),[Start Date]:[Start Date],<DATE(2025, 8, 17))

That way you are adding two new criteria to your existing sumifs: one to check to see if the start date (presumably in a column called "Start Date") is after August 9th (and therefore the 10th or later, you could also use a ">=" evaluation term and then have it evaluate against the 10th) and a second to check to see if the start date is before the 16th (and therefore on or before the 15th). The net result is that it only sums those projects that start on the 10th, 11th, 12th, 13th, 14th, or 15th.

The collect() function basically lets you externalize the criteria a bit and make it even more powerful yet - likely you don't need it, but basically it just gathers data that you care about - so you could make this into something like this:

=sum(collect([Est. work hours]:[Est. work hours], [Assigned to]:[Assigned to], "Jenny",[Start Date]:[Start Date],>DATE(2025, 8, 9),[Start Date]:[Start Date],<DATE(2025, 8, 17)))

Here you are collecting only those values that meet your criteria and then summing them. Since "sumifs" exists, no big deal - but if you were trying to do something like a "standard-deviation-ifs" it is less viable and collect makes it doable. You can likely ignore it if sumifs is working for you and just add extra criteria to your sumifs.

Rachel Mickelson

Hi @Jgorsich! I appreciate the formulas you sent my way. I plugged them both into my sheet separately and changed the dates, and while I didn't get any sort of error message, the result that came back was "0," but it should have been "2" for the testing I'm doing. So I'm not sure what that means.

Jgorsich

It means it didn't find anything that met all of the criteria (sumifs and collect both have implied "and" functions - any criteria needs to meet EVERYTHING you've called out).

To find a value with the formulas exactly as I wrote them, you need at least 1 row where ALL of the following is true:

1. [Est. work hours] has a numeric value that is greater than 0
2. [Assigned to] has "Jenny" as the ONLY THING written in it (Not "Jenny Smith", for example).
3. [Start Date] is a date column and has a value of ONE of the following (not a range like "8/10/25 - 8/14/25", just a single date value):
   1. 8/10/25
   2. 8/11/25
   3. 8/12/25
   4. 8/13/25
   5. 8/14/25
   6. 8/15/25
   7. 8/16/25

If you don't have a row like that, it will sum to zero.

If you are never going to have a row like that, because your data is formatted in some other way, please provide screenshots for more help.