Sum Costs by Quarter

mjapr1976

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?

 

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.

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.

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