QTD
I just figured out to YTD but now I need to figure out to QTD. Can anyone help?
Best Answers

Looking at your column names, it looks like you have either changed the names since the screen shot or your metrics table is in a different sheet to the data. If the metrics table is in a different sheet you need to set up cross sheet references for both of the columns.
Take a look at: https://help.smartsheet.com/learningtrack/level3advancedusers/crosssheetformulas
If you name the close date column "Master  Actual Close Date" and the Booked $ column "Master  Booked" then your formula will be:
=SUMIF({Master  Actual Close Date}, MONTH(@cell) <=3, {Master  Booked})
But you need to set up the references, copying the formula alone will not work.

Here are the two suggestions I gave for how to calculate each quarter, just change the column references to the cross sheet references you created.
and this for Q2Q4, changing the month numbers (the part in bold) as appropriate for the quarter.
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= 4, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= 6)
But if you wanted the formula to be based on the string in your QTR column, so you don't have to change the month numbers for each quarter, .......
.....
you can put a formula into the formula instead of the month numbers (i.e. replacing the parts in bold).
This would be the formula:
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= ((VALUE(RIGHT(QTR@row)) * 12) / 4)  2, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= (VALUE(RIGHT(QTR@row)) * 12) / 4)
Answers

hi @Nic Sanchez
I had a look at your YTD post. I think you want to sum the booked column if the QTR is less than or equal to the current quarter.
Is that correct?
If so, you will need to do a little more that you did for YTD.
 You'll need to define the current quarter based on the date. There is not a function like YEAR to do this for you. If you need help with this, let us know which quarters you use  is 1 JanMar, 2 AprJun, 3 JulSep, 4 OctDec.
 As you have a Q in the QTR column, you will need to use CONTAINS rather than equals to match on just the number part.
Otherwise, the process is the same as for YTD.

KPH,
That is correct. I define the QTR as 1 JanMar, 2 AprJun, 3 JulSep, 4 OctDec. What would the formula be for CONTAINS?

1. To find the current quarter from today's date
You can use this formula.
=ROUNDUP((MONTH(TODAY()) / 12) * 4)
It takes the month number from the current date (a number between 1 and 12) divides it by 12 (to give you the month as a fraction of a year), multiplies it by 4 (to give a fraction of a quarter), then rounds up to the nearest whole number (the quarter).
2. To SUM values based on the quarter
I actually changed my mind and think RIGHT is going to be better than CONTAINS for you. RIGHT returns the right hand character. In your case the quarter number. So your formula would be:
=SUMIF(QTR:QTR, RIGHT(@cell) <= ROUNDUP((MONTH(TODAY()) / 12) * 4), Booked:Booked)
If the right hand character of the data in the QTR column is less than or equal to,
the current quarter (the bold part being the formula from step 1 above),
then sum the values in the Booked column.

KPH,
Thank you. Do I need to add extra columns to my reference sheet?

Is that image your data sheet? I thought the image in your fist post was your data with the QTR column?
If the data has Actual Close Date in it, then you don't match on the QTR column, you match on the month number of the date instead.
Is that what you want to do?

The second image is my data sheet, and the first image is my metrics sheet, where I am trying to collect the data.

If your columns look like this, with a date column called Actual Close Date and a Text/Number column called Booked ($), containing the numbers you want to sum:
The simplest solution is to use this formula for Q1
=SUMIF([Actual Close Date]:[Actual Close Date], MONTH(@cell) <= 3, [Booked ($)]:[Booked ($)])
and this for Q2Q4, changing the month numbers (the part in bold) as appropriate for the quarter.
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= 4, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= 6)
But if you wanted the formula to be based on the string in your QTR column, so you don't have to change the month numbers for each quarter, you could use this formula to find the month number of the last month in the quarter:
=(VALUE(RIGHT(QTR@row)) * 12) / 4
And this to find the start (just subtract 2):
=((VALUE(RIGHT(QTR@row)) * 12) / 4)  2
Then you can put that into the formula instead of the month numbers (i.e. replacing the parts in bold).
This would be the formula:
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= ((VALUE(RIGHT(QTR@row)) * 12) / 4)  2, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= (VALUE(RIGHT(QTR@row)) * 12) / 4)
And your metrics table would look like this:

Thank you for the leading hand. I used the formula, =SUMIF([Master  Actual Close Date]:[Master  Actual Close Date], MONTH(@cell) <=3, [Master  Booked]:[Master  Booked]) , and I got #unparseable.
Could this be because not every cell is filled in for value/date?

Looking at your column names, it looks like you have either changed the names since the screen shot or your metrics table is in a different sheet to the data. If the metrics table is in a different sheet you need to set up cross sheet references for both of the columns.
Take a look at: https://help.smartsheet.com/learningtrack/level3advancedusers/crosssheetformulas
If you name the close date column "Master  Actual Close Date" and the Booked $ column "Master  Booked" then your formula will be:
=SUMIF({Master  Actual Close Date}, MONTH(@cell) <=3, {Master  Booked})
But you need to set up the references, copying the formula alone will not work.

Good and bad news. The good news is that it did sum up the booked ($), but the bad is the total is incorrect.

Can you figure out why the total is incorrect? If not, can you share what the total is and what the source data is?

I found the problem. It was my error. Thank you for all your help. How calculate for Q2 (Apr  Jun)?

Here are the two suggestions I gave for how to calculate each quarter, just change the column references to the cross sheet references you created.
and this for Q2Q4, changing the month numbers (the part in bold) as appropriate for the quarter.
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= 4, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= 6)
But if you wanted the formula to be based on the string in your QTR column, so you don't have to change the month numbers for each quarter, .......
.....
you can put a formula into the formula instead of the month numbers (i.e. replacing the parts in bold).
This would be the formula:
=SUMIFS([Booked ($)]:[Booked ($)], [Actual Close Date]:[Actual Close Date], MONTH(@cell) >= ((VALUE(RIGHT(QTR@row)) * 12) / 4)  2, [Actual Close Date]:[Actual Close Date], MONTH(@cell) <= (VALUE(RIGHT(QTR@row)) * 12) / 4)
Help Article Resources
Categories
Check out the Formula Handbook template!