Help on formula: Monthly savings calculation

Grace
Grace ✭
edited 12/09/19 in Formulas and Functions

Hi,

Please need your help on how I can calculate my monthly savings on completed projects. I am currently using the below formula but couldn't get it right.

=SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], <=[Summary Info 3]41, Status:Status, "completed")

Kind regards,

Grace

savings.png

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi Grace,

    Your formula looks correct from a syntax standpoint.

    Can you provide me with the result you're getting as well as the result you're expecting to get?

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Shaine,

    On the example I previously provided, the project was completed in March with 100k savings but on the column Summary Info 2 for the month of March, it's showing zero.

    My expected result is to get 100k savings for the project that was completed in the month of March.

    Overall I wanted to calculate the total savings that are achieved every month for completed projects.

    I'm also not quite sure if the dates in column Summary Info 3 helps. If there's a more logical way to get the amount of savings achieved between the first and end of every month that would be better.

    Kind regards,

    Grace

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi Grace,

    Thanks for clarifying. You might consider removing the "summary info 3" column entirely, and instead using a formula that will sum completed projects that have the specific month by using the MONTH function. For example:

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], MONTH(@cell) = 3, Status:Status, "Completed")

    Instead of having your formula reference another column for date, you can use MONTH(@cell) = 3 instead, and your function will only include cells with 3 for the month. 

    This will give you a more accurate SUM, as it will only include dates for that month. You'll need to change the MONTH criteria to do this for all of the other months:

    January

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], MONTH(@cell) = 1, Status:Status, "Completed")

    February

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], MONTH(@cell) = 2, Status:Status, "Completed")

    Etc...etc

    If you're still not getting the correct SUM, then you may need to change your column types or make sure that you're not accidentally converting numbers into text with manual formatting. Right click on your date column and make sure it's set to the date type.

    Let me know if you're still experiencing issues after trying the above.

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Shaine,

    I got an error message saying Invalid Data Type with the above formula so I did a little bit of research around the community and I am so happy to find some answers. I used your formula as my main reference and here's what I came up.

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], IFERROR(AND(MONTH(@cell) = 1, YEAR(@cell) = 2018), false), Status:Status, "Completed")

    I have been raising a lot of questions for a few weeks now and I must say that I get responses so quickly! I was able to complete my project tracker in no time. I am so grateful for this community! Thank you for all your help! smiley

    Kind regards,

    Grace

     

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Shaine,

    Please can you also advise what formula would work best if the monthly savings is to be calculated cumulatively?

    Many thanks,

    Grace

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi Grace,

    Are you looking to add the total of all 12 months?

    If that's the case—based on the structure of your sheet—you might consider adding a SUM formula to that "totals" row that SUMs the range of cells where your SUMIFS functions are returning their monthly totals.

    Let me know if that's not what you're looking for and I can advise further with more details on what you're wanting.

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    Glad you got it working!

    That's what the Smartsheet Community is all about.

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Shaine,

    I am looking to add the savings from the previous month to the current month. I would like to show in my charts the savings target for the year and then show how much savings are earned per month and whether we are hitting the target.

    I have a column where project managers enter their savings for a particular project and then a summary that shows the total savings earned per month. My first request stated to just calculate exactly what was earned per month but realised that it would make more sense if the savings from the previous to the current month are added together so in the line graph it would show a better reporting.

    savings.png

    · Share on FacebookShare on Twitter
  • Robert S.
    Robert S. Employee

    Hello Grace,

     

    Apologies for the delay in response. It sounds like for each month listed, you'd like to show the total savings of projects that ended in that month and the previous month. So with the information available in your screenshot, you would want February to show 250,000, March to show 650,000 (February + March), and April to show 400,000 (March + April). If this is what you're looking for, you can do this by changing your existing formula slightly like this:

     

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], IFERROR(AND(OR(MONTH(@cell) = 1, MONTH(@cell) = 2), YEAR(@cell) = 2018), false), Status:Status, "Completed")

     

    This would be the formula for February, since it's looking both months 1 and 2. For March, you can change these to be 3 and 2, April would be 4 and 3, and so on.

     

    If you're looking for something different, please provide as much detail as possible on what you'd like to calculate and I'll be happy to help.

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Robert,

    As always, thank you for the quick response! The formula did work however I think this will only apply if there's completed projects from the previous month. What if there's no savings from a certain month/s? How can we capture the values?

    For example:

    January - 100k

    February - 100k (Jan) + 100k (Feb) = 200k

    March - 200k (Jan+Feb) + 100k (Mar) = 300k

    April - 300k (Jan+Feb+Mar) + 0 (Apr) = 300k

    May - 300k (Jan+Feb+Mar+Apr) + 0 (May) = 300k ...and so on

    Kind regards,

    Grace

     

    · Share on FacebookShare on Twitter
  • Robert S.
    Robert S. Employee

    Hi Grace,

     

    Thanks for the clarification on what you're looking for. This can be done by using <= in the MONTH criterion of your original formula. Here's how that could look for February:

     

    =SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], IFERROR(AND(MONTH(@cell) <= 2, YEAR(@cell) = 2018), false), Status:Status, "Completed")

     

    This will be a fully cumulative sum of the months that have a status of Completed, rather than just the month and it's previous month.

    · Share on FacebookShare on Twitter
  • Grace
    Grace ✭

    Hi Robert,

    Happy to say that the formula worked! Thank you so much for all the help! smiley

    Best,

    Grace

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!