Stumped on a SUMIFS for Fiscal Year (FY)

Christopher Sullivan

Hello everyone,

I am a bit stumped on a solution. I tend to not like to hardcode information into a formula. I was working through a solution to help manage a Business Development (BD) pipeline.

With the help of the community, I figured out how to do a YTD. But then I was thinking that it didn't make sense to have the BD operating outside of the FIN Department's FY.

I used this for the YTD calculation:

=SUMIFS([Dollars]:[Dollars], [Date]:[Date], YEAR(@cell) = YEAR(TODAY()))

How would you convert that to a Fiscal Year cal and then how would you do last FY cal, without any hardcoding or column creation or sheet lookup?

I am curious if it is possible. I keep getting stuck trying to figure out how to nest it right.

SJ Sellers

You will have to define your Fiscal Year somewhere, whether that is in your formula, or stored in another cell. Smartsheet needs to understand your fiscal year in order to compare the dates in your sheet to your year.

Here's the formula that will get you the results you want:

=SUM(COLLECT(Dollars:Dollars, Date:Date, AND(@cell >= FYStart#, @cell <= FYEnd#)))

In this example, I created two new Sheet Summary fields, one called FYStart and another called FYEnd. Then input the start and end of the fiscal year into those fields. If you don't want to hardcode the year start and end into the fields, you can use formulas to populate those dates if you can create a formula that defines your fiscal year start and end.

OR, you can input the formulas that identify your FY Start and End directly into the SUM COLLECT formula in place of the two Sheet Summary references. Like this

=SUM(COLLECT(Dollars:Dollars, Date:Date, AND(@cell >= FORMULA_FOR_START_DATE, @cell <= FORMULA_FOR_END_DATE)))

Christopher Sullivan

I ended up using part of your solution. However, I feel like I still need a way to make it as autopilot as possible. I was attempting to think of a way to define the fiscal year based on the TODAY() function, maybe with a combo of MONTH() and YEAR().

The problem, at least logically, is that the FY is October to September. What I initially thought might work if it was July to June.

But, I ended up using the defined dates in the Sheet Summary with:

=SUMIFS([Dollars]:[Dollars], [Date]:[Date], <[Current FY End]#, [Award Date]:[Award Date], >[Current FY Start]#)

It works so far with testing.