Formula for calculating installs for a given quarter. Q1, Q2, etc..

Options
steve50951
steve50951 ✭✭
edited 12/09/19 in Formulas and Functions

i have this formula for calculating installs YTD for a given property.

=SUMIFS({Mira Santi Install QTY}, {Mira Santi Install Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Not sure how to take this formula to use it to calculate installs for a given quarter. Do i just put the date range for the quarter? i.e Jan 1, 2019-March 30, 2019.

Is there a shortcut for Q1, Q2, etc...?

thanks,

Screen Shot 2019-03-27 at 9.03.02 AM.png

Tags:

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 03/27/19
    Options

    There's several ways to approach this, but I will touch on 2 of them. I'm going to use the month formula so you don't have to worry about leap years.

    first method

    you can use a helper column to determine what the quarter is. The formula will be something like

    =if(month(Date@row)<4,1,if(month(Date@row) < 7, 2,if(month(Date@row) < 10,3,4

    and that would be dragged down in the original sheet and referenced with your sumif

    second method

    The other option is to stack your sumif.

    =SUMIFS({Sum Range},{Date Range}, MONTH(@cell) > 3, {Date Range}, MONTH(@cell) < 7)

  • steve50951
    Options

    i tried using the 1st method. I added a helper column.

    Here is the formula is used just to start with, just to test it.

    =IF(MONTH([Install Date]@row < 4, 1,))

    it gave me an unparesable response

    Any suggestions?

    Thannks

    Screen Shot 2019-03-27 at 11.09.04 AM.png

    Screen Shot 2019-03-27 at 11.09.31 AM.png

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Try this. Also make sure it is a text/number column

    =IF(MONTH([Install Date]@row) < 4, 1)

  • steve50951
    Options

    That formula worked up until I added

    IF(MONTH([Install Date]@row) < 10, 3, 4)) part to the formula.

    so this worked

    =IF(MONTH([Install Date]@row) < 4, 1, IF(MONTH([Install Date]@row) < 7, 2))

    but this didn't

    =IF(MONTH([Install Date]@row) < 4, 1, IF(MONTH([Install Date]@row) < 7, 2), IF(MONTH([Install Date]@row) < 10, 3, 4))

     

    Screen Shot 2019-03-27 at 12.04.15 PM.png

    Screen Shot 2019-03-27 at 12.04.21 PM.png

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    =IF(MONTH([Install Date]@row) < 4, 1, IF(MONTH([Install Date]@row) < 7, 2, IF(MONTH([Install Date]@row) < 10, 3, 4)))

     

    You have an issue with your parenthesis, You don't need a closing parenthesis after the 2.

  • steve50951
    steve50951 ✭✭
    edited 03/28/19
    Options

    Ah! Of course. totally missed that. thanks.

    So do you think this formula will hold up over time?

    I used this formula in a rollup sheet that calculates installs by quarter

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Inv Fiscal QTR}, 1)

    In the property sheet i created a helper column where i put in the formula you gave me. It calculates the quarter where it was invoiced.

    =IF(MONTH([Invoiced Date]@row) < 4, 1, IF(MONTH([Invoiced Date]@row) < 7, 2, IF(MONTH([Invoiced Date]@row) < 10, 3, 4)))

    The formula seems to work so far in the roll up sheet.

    Just trying to make sure it will hold up over time.

    Thanks again for all your help! Really appreciate it!

    SGF

    Screen Shot 2019-03-27 at 2.39.14 PM.png

    Screen Shot 2019-03-27 at 2.39.30 PM.png

    Screen Shot 2019-03-27 at 2.41.19 PM.png

  • steve50951
    Options

    Ah! Thanks.

    So here is the formula i am using for an install rollup sheet.

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Inv Fiscal QTR}, 1)

    the "Inv Fiscal QTR" is the helper column i added to my property sheet.

    The formula seems to be working.

    Do you think it will hold up over time?

    thanks again for you help. It is much appreciated!

     

    Screen Shot 2019-03-27 at 3.09.17 PM.png

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 03/27/19
    Options

    Your sumifs will hold up. Your helper column has the potential to break, depending on how the users use the sheet, but the simple fix is to drag the helper column down again. Also there is no consideration for next year in these formulas, which means the next Q1 the formulas will also account for the previous Q1. To fix this you can put an if statement at the start of your Q1 that says

    =if(Year(Date@row) = Year(today()),

    then it will always return the information for this year.  If you want the most robust solution it is the more complicated sumifs.

     

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, Year(@cell)=Year(today()),{Wilson Crossing Install Date},month(@cell)&lt;4

    would be Q1

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, Year(@cell)=Year(today()),{Wilson Crossing Install Date},month(@cell)&gt;3,{Wilson Crossing Install Date},month(@cell) < 7

    would be Q2

    untested

  • steve50951
    steve50951 ✭✭
    edited 03/28/19
    Options

    almost there...

    So i updated my helper column "Inv Fiscal QTR" formula with

    =IF(YEAR([Invoiced Date]@row) = YEAR(TODAY()), IF(MONTH([Invoiced Date]@row) < 4, 1, IF(MONTH([Invoiced Date]@row) < 7, 2, IF(MONTH([Invoiced Date]@row) < 10, 3, 4)))).

    Works great.

    So with an updated helper column that will return the correct QTR info, can i keep my sumif formula?

    Sumif formula for my install rollup

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Inv Fiscal QTR}, 1)

    Is there a need for the more robust sumif statements?

    thanks,

    Steve

     

    Screen Shot 2019-03-28 at 7.56.40 AM.png

    Screen Shot 2019-03-28 at 8.03.25 AM.png

    Screen Shot 2019-03-28 at 8.03.34 AM.png

  • steve50951
    Options

    ok i shored up the formula in my helper column, "Inv Fiscal QTR" to ensure it will only pull the quarter of the current year.

    Since i did that, is there a reason the add the robust formulas to my sumifs?

    or can i leave the formula the way i had it?

    Here's what i have in the sumifs page

    =SUMIFS({Garden Grove Install QTY}, {Garden Grove Inv Fiscal QTR}, 1)

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Right now you can leave the formula as it is. The sumifs that I posted before would mean you wouldn't need the helper column at all. Which solution you us really just depends on how the users interact with the sheet. Personally I would leave it a while, see if it is a problem. If you end up having to fix the sheet a lot, I would switch to the second solution.

  • steve50951
    Options

    Perfect. Ill monitor for now. Thanks again!

    SGF

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    When referencing the same range twice within a SUMIFS or COUNTIFS formula, you can use the OR or AND functions. I find it helps cut down on the steps if you are referencing it multiple times and it helps keep the overall formula shorter since you don't have to keep referencing the range.

     

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, Year(@cell)=Year(today()),{Wilson Crossing Install Date},month(@cell)&lt;4)

    can also be written as...

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) < 4))

     

    For your Q2, 3, and 4 formulas where you are specifying a range for your months...

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, Year(@cell)=Year(today()),{Wilson Crossing Install Date},month(@cell)&gt;3,{Wilson Crossing Install Date},month(@cell) < 7)

    can also be written as...

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) < #, MONTH(@cell <#))

     

    The more you reference the same range, the more space you can save by using OR and AND statements. It also gives you the capability of using the OR to look for multiple different options without having to use individual COUNTIFS or SUMIFS and then adding those results together.

    Q1 could also look like this:

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, YEAR(@cell) = YEAR(TODAY()), {Wilson Crossing Install Date}, MONTH(@cell) = 1) + =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, YEAR(@cell) = YEAR(TODAY()), {Wilson Crossing Install Date}, MONTH(@cell) = 2) + =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, YEAR(@cell) = YEAR(TODAY()), {Wilson Crossing Install Date}, MONTH(@cell) = 3)

    just to cover months 1, 2, and 3. Or you could just plug in the OR (and double it up with an AND) to shorten it down to:

     

    =SUMIFS({Wilson Crossing Install QTY}, {Wilson Crossing Install Date}, AND(YEAR(@cell) = YEAR(TODAY()), OR(MONTH(@cell) = 1, MONTH(@cell) = 2, MONTH(@cell) = 3))

    .

    Just a few little tips when dealing with COUNTIFS and SUMIFS

    .

    I also suggest wrapping your YEAR(@cell) and your MONT(@cell) in an IFERROR statement so that the formula will still work if there are any blanks within the range.

     

    IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

    IFERROR(MONTH(@cell), 0) > 4

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!