Sum product with an index

[KRTop]

I am trying to do a comparison of current year to prior YTD financial information.

A	B	C	D	E	F	G
		Jan-20	Feb-20	Mar-20	Apr-20	May-20
4100.50​	Miscellaneous	20,840	16,691	20,087	20,722	50,600
4100.51​	Parking Garages	-	-	-	-	-
4100.51​	Professional Buildings	-	-	-	-	-
4100.65​	Residential	1,350	1,215	2,290	1,640	1,840

I want to be able to say I want the sum of all of the 4100.50 code in column A for the first 5 periods. So far I have this, but I would like to add an index in there so that I can just change the number of periods and not have to change the columns each month.

SUMPRODUCT((A:A=4100.50)*(C:G))

The 4100.50 would actually be coded to another tab in my workbook. I am trying to add an index for the C:G, but I keep getting an error.

 

[CA_Punit]

Where do you want the "Index"(No of period) in a different cell or can it be coded in the formula.

 

[KRTop]

I was thinking the index would be in the C:G, so C:index(C:K,5). Next month that would move to 6, but it would be coded to another tab.

 

[CA_Punit]

Use offset INSTEAD

Book1
	A	B	C	D	E	F	G	H	I
1			Jan-20	Feb-20	Mar-20	Apr-20	May-20
2	4100.5	Miscellaneous	20,840	16,691	20,087	20,722	50,600		128940
3	4100.51	Parking Garages
4	4100.51	Professional Buildings
5	4100.65	Residential	1,350	1,215	2,290	1,640	1,840
6
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT((A2:A5=4100.5)*OFFSET(C2:C5,,,,5))

 

[KRTop]

That worked! Thank you! I have never used an offset before.

 

[CA_Punit]

Glad it worked. Yes, offset is widely used formula in Advanced Excel.