Index, if sum, match, vlookup??

[akilfoyle1192]

Hi all,

I am getting stuck on how and or what may be the best way to navigate this data. I have values for injuries and I want to know how much shoulder injuries cost in 2015 and 2016.

Imagine if you will;
[TABLE="width: 500"]
<tbody>[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type of injury[/TD]
[TD]Body Part[/TD]
[TD]Nature of injury[/TD]
[TD][/TD]
[TD]
Total[/TD]
[TD]Medical[/TD]
[TD]Indemnity[/TD]
[TD]Voc[/TD]
[TD]Legal[/TD]
[TD]Other[/TD]
[TD]Job Mod[/TD]
[/TR]
[TR]
[TD]Sprain
[/TD]
[TD]Shoulder[/TD]
[TD]I did x[/TD]
[TD][/TD]
[TD]$1500[/TD]
[TD]$1200[/TD]
[TD]$300[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Dislocation[/TD]
[TD]Shoulder[/TD]
[TD]I was X[/TD]
[TD][/TD]
[TD]$3000[/TD]
[TD]$2000[/TD]
[TD]$500[/TD]
[TD]0[/TD]
[TD]$500[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Exposure[/TD]
[TD]Eye[/TD]
[TD]Blood in eye[/TD]
[TD][/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sprain[/TD]
[TD]Ankle[/TD]
[TD]I did x[/TD]
[TD][/TD]
[TD]$300[/TD]
[TD]$300[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Strain[/TD]
[TD]Hamstring[/TD]
[TD]I was x[/TD]
[TD][/TD]
[TD]$1500[/TD]
[TD]$1000[/TD]
[TD]$250[/TD]
[TD]0[/TD]
[TD]$200[/TD]
[TD]$50[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I want to know the total sum of all shoulder injuries, total sum of medical, indemnity, etc in 2015. Mind you the real data has 100+ rows.

Thanks in advance.
AK

 

[estevaoba]

Assuming Body part column is B and Total column is D, try this and copy across for the other columns:

=SUMIF($B$2:$B$100,"Shoulder",D$2:D$100)

Godspeed!

 

[akilfoyle1192]

Assuming Body part column is B and Total column is D, try this and copy across for the other columns:

=SUMIF($B$2:$B$100,"Shoulder",D$2:D$100)

Godspeed!

Any way to insert a second argument with that equation?

I would like it to now count if it is a Shoulder Sprain, or Elbow Sprain, then report that sum. (see below?)

=SUMIF($B$2:$B$100,"shoulder"=Sumif($C$2:$C$100,"Sprain",$D$2:$D$100),$D$2:$D$100)

 

[akilfoyle1192]

=SUMIF($B$2:$B$100,"shoulder"=Sumif($C$2:$C$100,"Sprain",$D$2:$D$100),$D$2:$D$100)

Sorry, in my sheet type of injury occurs after the body part. In the equation above should read "A" instead of "C"