Sum and Match Combined

[manthony]

Hi,

I want to sum rows using the match function. I need the match function to figure out what row to sum based on the value in column A then skip 2 columns and sum row X columns D-F. Any ideas? Below is an example.

	Column A	Column B	Column C	Column D	Column E	Column F
Row 1	#	Street	City	Value 1	Value 2	Value 3
Row 2	256​	Main	Los Angeles	700​	500​	300​
Row 3	145​	Washington	San Franisco	100​	200​	450​
Row 4	255​	Jefferson	San Diego	0​	750​	0​

In this example, I would use the match function to find 145 in column A and then sum columns D-F to get a total of 750.

 

[lrobbo314]

How about this.

Book1
	A	B	C	D	E	F	G	H	I
1	#	Street	City	Value 1	Value 2	Value 3		Lookup:	145
2	256	Main	Los Angeles	700	500	300		Total:	750
3	145	Washington	San Franisco	100	200	450
4	255	Jefferson	San Diego	0	750	0
Sheet2

Cell Formulas
Range	Formula
I2	I2	{=SUM(INDEX($D$2:$F$4,MATCH(I1,$A$2:$A$4,0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

You don't need to use Ctrl Shift Enter to array confirm that formula