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;

2015
Type of injury	Body Part	Nature of injury		Total	Medical	Indemnity	Voc	Legal	Other	Job Mod
Sprain	Shoulder	I did x		$1500	$1200	$300	0	0	0	0
Dislocation	Shoulder	I was X		$3000	$2000	$500	0	$500	0	0
Exposure	Eye	Blood in eye		$50	$50	0	0	0	0	0
Sprain	Ankle	I did x		$300	$300	0	0	0	0	0
Strain	Hamstring	I was x		$1500	$1000	$250	0	$200	$50	0

<tbody>
</tbody>

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"