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]

Two options for summing the entire year:

=SUMIFS($D$21:$AA$21,D10:AA10,">="&DATE($A$2,1,1),D10:AA10,"<="&DATE($A$2,12,31))

or

=SUMPRODUCT(--(YEAR($D$10:$AA$10)=$A$2),$D$21:$AA$21)

In general, SUMIFS works faster than SUMPRODUCT.

 

[eds18]

Hmm, neither seemed to work. My date cell is 42940 or July 24, 2017. Thinking it might have to do with that?

Row 21: Total Revenue
Row 10: MMM-YY (Date)

 

[eds18]

Sorry I understand. A2 was simply the year. Not the full date. Devil is in the details! Thank you!

 

[datbigdog]

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

Is there a way to do this where the sum row is also dynamic?

 

[Ahmed Cleo]

I have Same issue Please Help

=SUMIFS(INDEX('Profitability By Segment'!$1:$1048576,0,MATCH('Montly GP Act Vs Bud'!$C$3,'Profitability By Segment'!$6:$6,0)),'Profitability By Segment'!$B:$B,'Montly GP Act Vs Bud'!$B6,'Profitability By Segment'!$A:$A,'Montly GP Act Vs Bud'!$B$2)

First index I use it reflect month but I need it to dynamic YTD not Month only what I should use ??