Sum and Match Combined

manthony

New Member
Joined
Dec 5, 2016
Messages
40
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 AColumn BColumn CColumn DColumn EColumn F
Row 1#StreetCityValue 1Value 2Value 3
Row 2
256​
MainLos Angeles
700​
500​
300​
Row 3
145​
WashingtonSan Franisco
100​
200​
450​
Row 4
255​
JeffersonSan 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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
How about this.

Book1
ABCDEFGHI
1#StreetCityValue 1Value 2Value 3Lookup:145
2256MainLos Angeles700500300Total:
750
3145WashingtonSan Franisco100200450
4255JeffersonSan Diego07500
Sheet2
Cell Formulas
RangeFormula
I2I2{=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

Well-known Member
Joined
Dec 30, 2008
Messages
9,595
Office Version
2019
Platform
Windows
You don't need to use Ctrl Shift Enter to array confirm that formula (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,571
Messages
5,469,467
Members
406,654
Latest member
MR_EXCEL_16

This Week's Hot Topics

Top