Summing a dynamic range with index match

[lessh2o]

I am stumped, sorry to add yet another question to the compendium of knowledge already here on using index,match and sum together.

The goal is to say return the sum of XX number of months in the row matching "Income" and beginning with "Mar 17" within 'Sheet 2'.

I figured I should use a SUM(INDEX(MATCH(),MATCH())) formula with the second match identifying an array of cells.

Right now, I have something along the lines of:
=SUM(INDEX('Sheet 2'!$F$1:$AG$78,MATCH($R9,'Sheet 2'!$F$1:$F$78,0),MATCH(??))

$R9 is the lookup_value for the row

Any help would be greatly appreciated!

 

[lessh2o]

	A	B	C	D	E	F	G	H	I	J	K
1		Jan 16	Feb 16	Mar 16	Apr 16	May 16	Jun 16	Jul 16	Aug 16	Sep 16	Oct 16
2	Income	#	#	#	#	#	#	#	#	#	#
3	Expense	#	#	#	#	#	#	#	#	#	#
4	Net Income	#	#	#	#	#	#	#	#	#	#

<tbody>
</tbody>

This might help, I'd like to sum the values in red.

Want to match a row lookup_value for "Expense" and a col lookup_value that is "Mar 16" along with another reference cell giving the number of additional columns desired past the identified col lookup_value, in this case "3".

 

[lessh2o]

bump

any help would be greatly appreciated!

 

[Domenic]

Try...

A1:P4

16-Jan

16-Feb

16-Mar

16-Apr

16-May

16-Jun

16-Jul

16-Aug

16-Sep

16-Oct

Income

#

#

#

#

#

#

#

#

#

#

Expense

16-Mar

3

0

Expense

#

#

#

#

#

#

#

#

#

#

Net Income

#

#

#

#

#

#

#

#

#

#

<tbody>
</tbody>

P2:

=SUM(INDEX($B$2:$K$4,MATCH(M2,$A$2:$A$4,0),MATCH(N2,$B$1:$K$1,0)):INDEX($B$2:$K$4,MATCH(M2,$A$2:$A$4,0),MATCH(N2,$B$1:$K$1,0)+O2))

Hope this helps!

 

[lessh2o]

You're awesome, thank you Domenic.

I knew it was right in front of me, just couldn't think to do a sum(index():index(same col+fixed amount)) function!

 

[RicardoCubed]

This is really great Domenic!
I am curious if you know the formula one would use for conditional formatting of the range that is addressed by this great formula?
I would like to highlight the range based upon the selection.
Thanks in advance.