QTD

Nic Sanchez

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

KPH

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/learning-track/level-3-advanced-users/cross-sheet-formulas

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. 

KPH

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 Q2-Q4, 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)

KPH

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.

1. 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 Jan-Mar, 2 Apr-Jun, 3 Jul-Sep, 4 Oct-Dec.
2. 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.

Nic Sanchez

KPH,

That is correct. I define the QTR as 1 Jan-Mar, 2 Apr-Jun, 3 Jul-Sep, 4 Oct-Dec. What would the formula be for CONTAINS?

KPH

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.

Nic Sanchez

KPH,

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

KPH

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?

Nic Sanchez

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

KPH

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 Q2-Q4, 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:

Nic Sanchez

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?

KPH

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/learning-track/level-3-advanced-users/cross-sheet-formulas

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. 

Nic Sanchez

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

KPH

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

Nic Sanchez

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

KPH

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 Q2-Q4, 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)