Sum(if...between date range

[Pizzio]

I need a formula that will sum expense between year X and year Y. Now, should the year end be mid year the formula will need to be able to break out the expense by the number of months.

Example: End date 06/2017
The formula will need to sum the expense from 2011 through 2016 then ONLY 6 months of 2017 through divide by 6.

Clear as mudd right?

 

[Aladin Akyurek]

I need a formula that will sum expense between year X and year Y. Now, should the year end be mid year the formula will need to be able to break out the expense by the number of months.

Example: End date 06/2017
The formula will need to sum the expense from 2011 through 2016 then ONLY 6 months of 2017 through divide by 6.

Clear as mudd right?

To be a bit more clear than mud, scale down the issue... And post a small sample invoving 2011 and 2012,
end date 06/2012, and the expected result.

 

[Pizzio]

Using the information below, here is what I need.

I need to be able to sum expense between years AND be able to adjust for mid year dates.

Example:

Start date: 06/11
End Date 03/15

The end result should be 38,819.60

Book1
	A	B	C	D	E	F
3	2011	2012	2013	2014	2015	2016
4	10,000.00	10,500.00	11,025.00	11,576.25	12,155.06	12,762.82
Sheet1

 

[Aladin Akyurek]

Let A3:F4 house the sample you posted.

G2:H4 house specs and the result:

<TABLE style="WIDTH: 113pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=151><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2628516 class=xl67 height=19 width=77>Start date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=74 align=right>6/1/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>End date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3/1/2015</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19>Result</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>41,140.02</TD></TR></TBODY></TABLE>

H4, control+shift+enter, not just enter:

=SUM(IF($A$3:$F$3>=YEAR(H2),IF($A$3:$F$3<=YEAR(H3),A4:F4)))-
    (12-MONTH(H2))*(INDEX(A4:F4,MATCH(YEAR(H2),A3:F3,0))/12)-
    (12-MONTH(H3))*(INDEX(A4:F4,MATCH(YEAR(H3),A3:F3,0))/12)

 

[Pizzio]

This is perfect. Thank you!!