Index Match with Sum Function

[italy16]

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.

 

[KRice]

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]

Is this what you want

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3
4			Salaries	Printing	Salaries	Office
5		Sales	10	11	22	13				Sales	32
6		Production	14	15	16	17				Salaries
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
L5	L5	=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]

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]

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

 

[KRice]

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
	A	B	C	D	E	F	G	H
1		Sales	Marketing	Engineering			Engineering	Sales
2	Salaries	100	75	50		Salaries	85	150
3	Salaries	50	40	35		Bonus	10	2
4	Bonus	2	5	10
Sheet6

Cell Formulas
Range	Formula
G2:H3	G2	=SUMPRODUCT(($B$1:$D$1=G$1)*($A$2:$A$4=$F2),$B$2:$D$4)

 

[italy16]

Thank you! it worked!

 

[KRice]

That's good to hear...happy to help!