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

steve50951

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,

L_123

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

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

L_123

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

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

steve50951

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))

 

L_123

=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

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

steve50951

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!

 

L_123

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

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

 

steve50951

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

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

Perfect. Ill monitor for now. Thanks again!

SGF

Paul Newcome

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