# Index Match with Sum Function

#### italy16

##### New Member
Hello All - Is there a way to use the index match function with the sum function? I'm using index match but noticing its only pulling the 1st value in the data set (or array) instead of summing the value. For example, if I was searching rows for "Sales" and then columns for "salaries" and I have salaries listed multiple times, it would only pull the salary from the 1st time it was listed. Instead, I was hoping it would sum up all the values when "salaries" was listed.

Thank you very much in advance.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### KRice

##### Well-known Member
It sounds as if you'll want to use either SUMIFS or SUMPRODUCT. If you post a small example to show where Sales and Salaries are in your sheet layout, we can offer some more specific solutions. What version of Excel are you using? You might want to update your profile to include this information, as some solutions depend on certain versions.

#### CA_Punit

##### Well-known Member
Is this what you want

Book1
BCDEFGHIJKLM
3
4SalariesPrintingSalariesOffice
5Sales10112213Sales32
6Production14151617Salaries
7
8
9
10
Sheet1
Cell Formulas
RangeFormula
L5L5=SUM(IFERROR(INDEX(\$D\$5:\$G\$6,MATCH(K5,\$C\$5:\$C\$6,0),SEARCH(K6,\$D\$4:\$G\$4)^0*(COLUMN(\$D:\$G)-COLUMN(\$D:\$D)+1)),0))

#### italy16

##### New Member
Thanks for the fast response! Yes, think this is what I'd like. Thank you. Let me try it out and then if not, I'll post a screen shot of my data to troubleshoot. Thanks again

#### italy16

##### New Member

Couldn't quite get the formula correct. Would like to say if the row = Engineering then sum up the salaries (50+35=85). I think this was similar to your example, but I might be missing something. Thank you

#### Attachments

• index function.png
4.9 KB · Views: 6

#### KRice

##### Well-known Member
Here is one approach. Note that I've used the row/column headings in the summary table for matching with the row/column headings in the data table...you could hardwire these into the formula if desired:
Book1
ABCDEFGH
1SalesMarketingEngineeringEngineeringSales
2Salaries1007550Salaries85150
3Salaries504035Bonus102
4Bonus2510
Sheet6
Cell Formulas
RangeFormula
G2:H3G2=SUMPRODUCT((\$B\$1:\$D\$1=G\$1)*(\$A\$2:\$A\$4=\$F2),\$B\$2:\$D\$4)

#### italy16

##### New Member
Thank you! it worked!

#### KRice

##### Well-known Member
That's good to hear...happy to help!

Replies
12
Views
132
Replies
11
Views
189
Replies
1
Views
187
Replies
0
Views
74
Replies
3
Views
96