Index Match with Sum Function

italy16

New Member
Joined
May 9, 2014
Messages
34
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.
 

Some videos you may like

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
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 9, 2014
Messages
34
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
Joined
May 9, 2014
Messages
34

ADVERTISEMENT

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
    index function.png
    4.9 KB · Views: 6

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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)
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
That's good to hear...happy to help!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,523
Members
410,689
Latest member
ConfuzzledThomas
Top