Capacity formula

Shaun Bailey

I am trying to add to this formula "<+" &{end week}, Weekend@row

=COUNTIFS({Name}, [Primary Column]@row, {Week}, start@row, {Year}, [Column3]@row, {SKill}, "LTE") * 60

I want this formula to look and see the end week and add 60 for every week of the project. Help columns and example below. Right now this formula tracks 60 for the week start but I want to track 60 each week of the project.

Brian_Richardson

You can use WEEKNUMBER

For the number of weeks multiplied by 60 do = (WEEKNUMBER (End@row) - WEEKNUMBER (Start@row) )*60

Shaun Bailey

Are you saying to combine it with =COUNTIFS({Name}, [Primary Column]@row, {Week}, start@row, {Year}, [Column3]@row, {SKill}, "LTE") * 60

This formula *60 if the name is found in the primary column and if the start week is in the start row and the year is in column 3 plus if the skill LTE is selected. How would I implement this with = (WEEKNUMBER (End@row) - WEEKNUMBER (Start@row) )*60 to make sure if the project runs 15 weeks it adds 60 to all 15 weeks?

Shaun Bailey

I've tried to utilize Chatgpt help as well, but the sheet does not support ( "< ="& )…. I think I am at an impasse where i likely will have to do this manually.

=COUNTIFS({Name}, [Primary Column]@row, {Current Week Number}, ">=" & [Start Week Number]@row, {Current Week Number}, "<=" & [End Week Number]@row, {Year}, [Column3]@row, {Skill}, "LTE") * 60

Brian_Richardson

Sorry Shaun I'm not quite following. Taking a guess though, it sounds like, from one sheet you have rows where you've defined combinations of name, year, start, end, and skill. On a second sheet you have some detailed rows with the same information, potentially multiple rows. And you're trying to add up the number of weeks from the second sheet from the start of the item to today, where the row matches the conditions on the summary rows in the first sheet, then multiply those weeks by 60.

Right?

Couple of things - & is not an operator in Smartsheet. If you're trying to concatenate two pieces of text you use +. But I'm not clear why you'd be concatenating? Also, using " < = " is just putting text into the middle of your formula, which isn't going to work. You don't need to wrap the operators in quotes unless you want to show that as text in the cell.

I'm not clear why you're using COUNTIFS instead of SUMIFS? COUNTIFS will only tell you how many rows match the conditions, it sounds like you want a total of the weeks though? So I'd use SUMIFS for that.

If I'm right in my summary above, then you should be able to do this:

1. On both the summary sheet and the detailed sheet have a column for Start Week Number with the column formula = WEEKNUMBER (Start@row) . I'm assuming there's a Start date column somewhere called Start, replace with your actual start date column name.
2. On both the summary sheet and the detailed sheet have a column for End Week Number with the column formula = WEEKNUMBER (End@row). Again replace with your actual end date column.
3. On the detailed sheet have a column for Weeks To Today with the column formula = WEEKNUMBER (Today()) - [Start Week Number]@row That will give you the number of weeks for the detailed rows from the start date of that row to today.
4. On the summary sheet you can then use this formula in a column to get the sum of number of weeks that match the first sheet row conditions: = SUMIFS ( {Weeks to Today}, {Name}, [Primary Column]@row, {Start Week Number}, >=[Start Week Number]@row, {End Week Number], <= [End Week Number]@row, {Column 3}, "2024", {Skill}, Skill@row)*60

In the formula above, the {} references are cross sheet references to the same name columns on your detailed sheet.

Shaun Bailey

Yes I do have an index sheet. I will try your suggestions and let you know if that worked. Here is a basic replica of my index sheet.