How to find a value based on fiscal year end

[cobo1981]

I don't know why I'm struggling with this so much since it seems like a simple formula.

So let's assume I have 3 or more fiscal year ends for 3 years, 2/28, 3/31, 5/31

Column A Column B Column C
2/28/2012 2/28/2013 2/28/2014
3/31/2012 3/31/2013 3/31/2014
5/31/2012 3/31/2013 5/31/2014

So what I need is a formula that will return a values from column D if the date of the invoice falls between the above dates (based on fiscal year), and sum them together.

I hope that's enough information but if you need more please let me know.

[gak67]

You could use a SUMIFS function.

Please Login or Register  to view this content.

Replace the square brackets with the appropriate ranges or values.

[FDibbins]

Take a look at using SUMIFs() for this. If you provide a small sample workbook, I will see what I can put together for you

[cobo1981]

Here is a sample.

[FDibbins]

This may not line up exactly with your YE dates, so you can modify if needed...
=SUMPRODUCT((YEAR($D$3:$D$17)=2013)*$E$3:$E$17)

[cobo1981]

That will only sum values that have the same calendar year end. What I need is based on FYE.

I would like to sum values, per fund, that fall between two dates.
So in case of fund RDSE I need to add values that fall between 3/1/2013 and 2/28/2014.

Not keep in mind that there are multiple funds and multiple year ends so formula should take all those into consideration.

I've attached updated worksheet.

[gak67]

Can you please clarify, is it the end date that is important? Or would those same RDSE amounts also be included in the previous year as they were started before 31 March 2013?

[cobo1981]

End date is important because that determines year end for each fund.

So in case of RDSE:

4/1/2012 to 3/31/2013, FYE is 3/31/2013, Total should be = 875.12
4/1/2013 to 3/31/2014, FYE is 3/31/2014, Total should be = 721.24 (wrongly placed in 2013 in my sheet, should me under 2014)

In other words I would like to have totals per fund and per FYE. Totals should be summed per year.

Sorry if I make no sense, it's been a long day.

[cobo1981]

Let me add this as well:

RDSE 31-Mar 9/1/2011 2/29/2013 875.12 - FYE = 3/31/2013 so it should sum under 2013
RDSE 31-Mar 9/1/2012 8/31/2013 308.58 - FYE = 3/31/2014 so it should sum under 2014 (anything after FYE belongs to the following year)
RDSE 31-Mar 9/1/2012 8/31/2013 38.70 - FYE = 3/31/2014 so it should sum under 2014 (anything after FYE belongs to the following year)
RDSE 31-Mar 9/1/2012 8/31/2013 373.96 - FYE = 3/31/2014 so it should sum under 2014 (anything after FYE belongs to the following year)

[gak67]

This in I3 and dragged down and across should work:

Please Login or Register  to view this content.

[cobo1981]

Looks good, thanks so much for your help.