Sum months prior to current month

tbaker555

Hello, hoping someone can help me after trying to search for this issue on the boards.

I have columns for every month of the year tracking project $ spent as well as projected to be spent in the future months. I'm trying to use a formula that will track the $ in the columns prior to the current month that can automatically update to show our spent to date. I'm not sure if this is a very easy formula or if I need to adjust how my columns are labeled? Thanks in advance!

Mike TV

@tbaker555

There are different ways to accomplish the same thing in SmartSheet. Here's the only way I currently know how.

Create a column which tells you the current month with the =MONTH(TODAY()) formula. Then string multiple IF statements together that will SUM the correct month columns that would be added together based on the current month. Here's a portion of that formula:

=IF([Current Month]@row = "1", "n/a", IF([Current Month]@row = "2", [January $]@row, IF([Current Month]@row = "3", SUM([January $]@row, [February $]@row), IF([Current Month]@row = "4", SUM([January $]@row, [February $]@row, [March $]@row), IF([Current Month]@row = "5", SUM([January $]@row:[April $]@row))))))

You can see by the formula I did the SUM two ways. One way is by pointing to each particular cell to add together while you'll need if your Monthly $ columns are separated by other columns inbetween. The 2nd way is to point to a range of cells like [January $]@row:[April $]@row if there's no columns inbetween each Monthly $ column.

Sandra Guzman

Hi @tbaker555!

@Matt C. is correct in that there are different ways to accomplish this. I think it really comes down to how you would like to display your data. If you already have the columns listed out, another easy way to accomplish this could be to create a report and use filters and the summary to get what you are looking for.

Here is a quick example of utilizing the reports to get the sum for the totals for each of the columns.

It really comes down to how you are managing your existing sheet with the data you are inputting. If you need to show data based on a timeframe, this is where you could use the filter to show for that as well. You would need to ensure that your data is captured in your sheet in order for you to use it this way.

All the best!

Sandra

Mike TV

@tbaker555

There are different ways to accomplish the same thing in SmartSheet. Here's the only way I currently know how.

Create a column which tells you the current month with the =MONTH(TODAY()) formula. Then string multiple IF statements together that will SUM the correct month columns that would be added together based on the current month. Here's a portion of that formula:

=IF([Current Month]@row = "1", "n/a", IF([Current Month]@row = "2", [January $]@row, IF([Current Month]@row = "3", SUM([January $]@row, [February $]@row), IF([Current Month]@row = "4", SUM([January $]@row, [February $]@row, [March $]@row), IF([Current Month]@row = "5", SUM([January $]@row:[April $]@row))))))

You can see by the formula I did the SUM two ways. One way is by pointing to each particular cell to add together while you'll need if your Monthly $ columns are separated by other columns inbetween. The 2nd way is to point to a range of cells like [January $]@row:[April $]@row if there's no columns inbetween each Monthly $ column.

Sandra Guzman

Hi @tbaker555!

@Matt C. is correct in that there are different ways to accomplish this. I think it really comes down to how you would like to display your data. If you already have the columns listed out, another easy way to accomplish this could be to create a report and use filters and the summary to get what you are looking for.

Here is a quick example of utilizing the reports to get the sum for the totals for each of the columns.

It really comes down to how you are managing your existing sheet with the data you are inputting. If you need to show data based on a timeframe, this is where you could use the filter to show for that as well. You would need to ensure that your data is captured in your sheet in order for you to use it this way.

All the best!

Sandra

tbaker555

Thank you @Matt C. and @Sandra Guzman! The formula worked perfectly for what I needed. The report is another option we may use in the future, but right now we're keeping everything in one sheet so we'll start there.

I really appreciate the quick responses!