How to Sum rows in a column based on date

EGBJ

I have a sheet set-up with distinct numbers project numbers in the rows with columns containing the months of the year. Each month I drop in actual activity for the current month and someone will forecast their spend for the remaining months of the year. Is there a way to have a formula that adds up the columns with actual activity and another formula would sum the remainder of the year. For example I have actuals for January - March which I would like to sum with April - December summed together in another formula. Next month I would drop in April actuals and I would want the formulas to add up January - April and May-December. In Excel I would use the SUM and OFFSET formulas with an input setup to identify the month. Is this possible in SmartSheets?

Paul Newcome

In that case, we can utilize it. We don't need to remove it or add in another.

In each of the month columns, manually enter the month numbers.

1 - 2 - 3 - 4 - so on and so forth.

Then to get the actuals you would use:

=SUMIFS(January@row:December@row, January$1:December:1, @cell< MONTH(TODAY()))

And for the forecast:

=SUMIFS(January@row:December@row, January$1:December:1, @cell>= MONTH(TODAY()))

Paul Newcome

Are you able to provide screenshots for context?

EGBJ

@Paul Newcome Here is a screen shot. Two columns need to be added. Assuming we just finished March of the year and would include actuals with April- December representing a forecast. The first column "year to date actuals" (includes Jan-Mar) would have $75K for the first row and $10K for the next. The second column "remainder of year" (includes Apr-Dec) would show $110K for the first row and $21K for the second row. At the end of April, the actuals would be entered into the April column as well as making changes to the forecast based on actuals. The year to date actuals column would update to include January - April actuals while the remainder of year would change to include May-December. Each month the columns would update.

Paul Newcome

What is in the top row? Are you able to use that as a "helper row"? If not then you would have a very long formula with nested IFs. If so, we could use a SUMIFS with some manual entry on the top row to really simplify the formula.

EGBJ

@Paul Newcome The top row is just a header to summarize the project by open and close. It can be deleted or a new row can be added above it.

Paul Newcome

In that case, we can utilize it. We don't need to remove it or add in another.

In each of the month columns, manually enter the month numbers.

1 - 2 - 3 - 4 - so on and so forth.

Then to get the actuals you would use:

=SUMIFS(January@row:December@row, January$1:December:1, @cell< MONTH(TODAY()))

And for the forecast:

=SUMIFS(January@row:December@row, January$1:December:1, @cell>= MONTH(TODAY()))

EGBJ

@Paul Newcome Thank you for the assistance. The numbers are added along with the formula. There is an "Unparseable" error that showed up.

One other question will the year to date show up as January - March until April results are loaded in early May or will it pick up the forecast now?

Paul Newcome

Sorry about that. I had a typo.

=SUMIFS(January@row:December@row, January$1:December$1, @cell>= MONTH(TODAY()))

It will be based on today's date. So on May 1, April will shift from the actual to the forecast. There is no easy way to get it to shift based on when data was entered.

EGBJ

@Paul Newcome Thank you!!!! That worked great! One more question, is it possible to convert the formula to a column formula? The following error was received when I tried. It has something to do with the absolute reference, but I don't see a workaround.

Paul Newcome

Unfortunately there is no workaround for this particular instance. You will need to drag-fill the existing rows but then the auto-fill feature should kick in for new rows as they are added as long as there are at least two rows immediately above or immediately below the new row.

EGBJ

@Paul Newcome Thank you, I appreciate all the assistance.

Paul Newcome

Happy to help. 👍️