SUMIF with an INDEX MATCH for Year to Date Dynamic Formula

[eds18]

This is probably very basic but I'm struggling with it.

I have monthly financials from 2016 to 2017. January 2016:December 2017.

I would like to be able to pull YTD figures based on Month and Year.

A1: Month: Feb
A2: Year: 2017

In A3 I would have Sum of YTD Feb 2017. If I change A2 to 2016, I would have Sum of YTD Feb 2016.

I was able to do it without Sum for the Month actual but YTD I'm struggling with.

=IFERROR(INDEX(Company!$D$21:$FM$21,MATCH(Company!$C$10,Company!$D$10:$FM$10)),)

Row21: Revenue Line
C10: Monthly Period from Start Date (If Feb 2017, C10 would be 14 (i.e. 12 + 2) because we are starting in Jan 2016)
Row 10: Monthly Period from Start Date

Any help would be much appreciated.

 

[Tetra201]

If you put dates in row 10 (1-Jan-2016, 1-Feb-2016, 1-Mar-2016, etc.) then you can use SUMIFS like this:

=SUMIFS($D$21:$AA$21,$D$10:$AA$10,">="&DATE($A$2,1,1),$D$10:$AA$10,"<="&EOMONTH(--("1-"&$A$1&"-"&$A$2),0))

What I don't understand is why your 24-month data covers 166 columns (D through FM).

 

[eds18]

What I don't understand is why your 24-month data covers 166 columns (D through FM).

Sorry for the confusion, I'm actually showing months from Jan 2016 to Dec 2027 so it goes through FM.

What does A2 and A1 in your DATE() and EOMONTH() pat of the formula do? I'm still getting zero with your formula.

 

[eds18]

Sorry for the confusion, I'm actually showing months from Jan 2016 to Dec 2027 so it goes through FM.

What does A2 and A1 in your DATE() and EOMONTH() pat of the formula do? I'm still getting zero with your formula.

Sorry! I'm an idiot. This worked perfectly.

Next question for you.....If I wanted to do the same for quarters to date. For example, if A1 and A2 were April 2017, it would pull Q2 2017 to date, so just March and April 2017. Possible?

 

[Tetra201]

Does your Q2 really start in March?

 

[eds18]

Nope, April actually.

 

[Tetra201]

Try this for QTD:

=SUMIFS($D$21:$AA$21,$D$10:$AA$10,">="&DATE($A$2,(ROUNDUP(MONTH("1-"&$A$1)/3,0)-1)*3+1,1),$D$10:$AA$10,"<="&EOMONTH(--("1-"&$A$1&"-"&$A$2),0))

 

[eds18]

This is extremely help! Thank you seriously! It worked no problem.

Two other questions:

(1) If I wanted to find the QTD for year prior what would I change in ,">="&DATE($A$2,(ROUNDUP(MONTH("1-"&$A$1)/3,0)-1)*3+1,1),$D$10:$AA$10,"<="&EOMONTH(--("1-"&$A$1&"-"&$A$2),0))? So if the dynamic cell was August 2017, it would Sum July + August 2016?

(2) If I wanted to sum the entire QTD budget? So if it was August 2017, it would sum July+Aug+Sep 2017. Possible?

Thank you Tetra!

 

[Tetra201]

For PYQTD, just subtract 1 from the year:

=SUMIFS($D$21:$AA$21,$D$10:$AA$10,">="&DATE($A$2-1,(ROUNDUP(MONTH("1-"&$A$1)/3,0)-1)*3+1,1),$D$10:$AA$10,"<="&EOMONTH(--("1-"&$A$1&"-"&($A$2-1)),0))

For the entire QTR budget, try this:

=SUMIFS($D$21:$AA$21,$D$10:$AA$10,">="&DATE($A$2,(ROUNDUP(MONTH("1-"&$A$1)/3,0)-1)*3+1,1),$D$10:$AA$10,"<"&DATE($A$2,(ROUNDUP(MONTH("1-"&$A$1)/3,0)-1)*3+4,1))

 

[eds18]

You're incredible with this! If I could like it over and over I would. Might as well ask but how would you do Sum the entire year no matter the month but only dependent on the year?

=SUMIF($D$10:$AA$10,"="&YEAR($A$2),$D$21:$AA$21)

A2 = 7/1/2017

Is it possible to stick in a Year() before looking in Range (column 10)?