Sum Collect or If Statements

Question

I’m trying to build a formula that can achieve the following logic, but I’m struggling to get it working correctly. I have a data sheet with around 100 project rows. Each row contains a Fab Start date and a Fab Finish date (along with many other fields). Some projects run across two consecutive months. In my metrics sheet, I have a date column containing the 1st of each month for 12 months. This sheet calculates how many days each project consumes in each month. For projects where the start and finish dates fall within the same month, I already have a working formula: Code =SUM(COLLECT( {Workshop Days}, {Start Date}, >= DATE(YEAR(Date@row ), MONTH(Date@row )), {Start Date}, <= DATE(YEAR(Date@row ), MONTH(Date@row ) + 1), {Finish Date}, >= DATE(YEAR(Date@row ), MONTH(Date@row )), {Finish Date}, <= DATE(YEAR(Date@row ), MONTH(Date@row ) + 1))) Now I’m trying to build a formula that handles projects spanning two consecutive months. Specifically, I need to calculate the number of working days from the Fab Start date to the end of the starting month, so I can allocate the correct number of days to that month (with the remaining days allocated to the following month). I’m trying to avoid using helper columns because I want this to work for any month in any year without modifying the data sheet. Here’s the logic I need, in this order: * If the Fab Start and Fab Finish dates are in the same month:* Skip the row completely. * If the Fab Start date is today or later:* Calculate the number of working days from today to the end of the month. * If the Fab Start and Fab Finish dates are in consecutive months:* Calculate the number of working days from the Fab Start date to the end of that month. I want to sum these results across all matching rows, but I’m not sure how to structure the conditions and calculations inside a SUM(COLLECT()) or SUMIFS formula. Once I can make this work i can apply the same logic to get the days allocated to the ending month. Once the project falls into the current month i will just return the total days remaining. How can I make this work in Smartsheet?

Paul Newcome · Answer

You will need two helper columns in the source sheet. One will be for the first month and the other will be for the second month. It will calculate the total on a row by row basis.

The [Days In First Month] column formula would be:

=DAY(IFERROR(DATE(YEAR([Fab Start]@row), MONTH([Fab Start]@row) + 1, 1), DATE(YEAR([Fab Start]@row) + 1, 1, 1))) - DAY([Fab Start]@row)

The [Days In Second Month] column formula would be:

=IF(MONTH([Fab Start]@row) <> MONTH([Fab Finish]@row), DAY([Fab Finish]@row), 0)

Then your metrics sheet would use a SUMIFS where we sum up the {Days In First Month} column if the {Fab Start} column is the same month and year for your month in the metrics sheet. Then we do the same with the {Days In Second Month} column where the {Fab Finish} is the same month and year.

Assuming you are using the [Fab Completed Month] in your screenshot as the month to add up for, it would look something like this:

=SUMIFS({Days In First Month}, {Fab Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Fab Completed Month]@row), IFERROR(YEAR(@cell), 0) = YEAR([Fab Completed Month]@row))) + SUMIFS({Days In Second Month}, {Fab End}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Fab Completed Month]@row), IFERROR(YEAR(@cell), 0) = YEAR([Fab Completed Month]@row)))

Techspan CT · Answer

This is the metrics sheet for context. currently its only counting projects that fall entirely within the month.

Techspan CT · Answer

Technically yes but to date the longest has always been less than 8 weeks so im only attempting to make this work for any consecutive 2 month period.