Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sum Costs by Quarter

mjapr1976
mjapr1976 ✭✭
edited 12/09/19 in Archived 2017 Posts

I have purchase requests coming to me from a smartsheet form and it is capturing costs of each request. I have a "created on" column open in the sheet. I would like to sum costs of all requests per quarter. This information is being captured in a sight and broken down into quarters and Year to Date on the sight for 2017. A new widget will be added for 2018 but I am operating on the same sheet. So, I would like to be able to differentiate in the smartsheet 2017 and 2018 costs as well.  Can someone help with the formula I would need to do this?

 

· Share on FacebookShare on Twitter

Comments

  • bhope51
    bhope51 ✭✭✭

    You could build out a nested if statement like this.

    =IF(MONTH(Created1) = 1, "FQ 1", IF(MONTH(Created1) = 2, "FQ 1", IF(MONTH(Created1) = 3, "FQ 1", IF(MONTH(Created1) = 4, "FQ 2", IF(MONTH(Created1) = 5, "FQ 2", IF(MONTH(Created1) = 6, "FQ 2", IF(MONTH(Created1) = 7, "FQ 3", IF(MONTH(Created1) = 8, "FQ 3", IF(MONTH(Created1) = 9, "FQ 3", IF(MONTH(Created1) = 10, "FQ 4", IF(MONTH(Created1) = 11, "FQ 4", IF(MONTH(Created1) = 12, "FQ 4"))))))))))))

    But double check the number of ) at the end first. I think its right.

    · Share on FacebookShare on Twitter
  • bhope51
    bhope51 ✭✭✭

    If you want to only see the year though, just create a column with the formula below. I would filter by that.

    =YEAR(Created1)

    Hope these help.

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    To get FQ #, this is better:

    ="FQ " + INT((MONTH(Created1) + 2) / 3)

    but to sum, you don't need the FQ, just the number

    To get just which quarter it is in, something like this would work:

    =SUMIFS([Column6]:[Column6], Created:Created, INT((MONTH(@cell) + 2) / 3) = 2)

    Note: I use SUMIFS instead of SUMIF because most of them will expand to multiple criteria and I prefer the syntax order of SUMIFS in general.

    [Column6] is the column being summed and I am looking for quarter 2.

    To look for the current quarter (on the day you are viewing it)

    =SUMIFS([Column6]:[Column6], Created:Created, INT((MONTH(@cell) + 2) / 3) = NT((MONTH(TODAY()) + 2) / 3))

    To add the year:

    =SUMIFS([Column6]:[Column6], Created:Created, INT((MONTH(@cell) + 2) / 3) = 2, Created:Created, YEAR(@cell) = 2017)

    for 2017 Q2

    and so on.

    I hope this helps.

    Craig

     

     

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories