Using SUMIF on INDEX MATCH

[Hammerhance]

hi there,

I am using INDEX MATCH to identify a range of figures across sheets, but I'm having difficulty then adding in SUMIF to calculate multiple occurances.

This sheet is 'Events' and the formula I am using in column E is =INDEX('2013'!D:D,MATCH(Events!A5,'2013'!A:A,0))

Advanced Engineering	NEC	November	£4,355	£4,355
AP&M	Olympia	May	£10,730	£10,730
Autumn Furniture Show	Stonleigh Park	October	£0	#N/A
BETT	Excel	January	£13,275	£13,275
Biofuels Expo	Stoneleigh	September	£3,920	£2,985

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

This sheet is '2013' Biofuels Expo Ocober 2013 £2,985 Biofuels Expo Ocober 2013 £935 <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The figure in column E on the first sheet, should read £3920, the sum of the above two figures, but I can't get the formula to work.

Please can someone help

Many thanks

 

[Jonmo1]

Welcome to the board.

That's a job for SUMIF
Try
=SUMIF('2013'!A:A,A5,'2013'!D:D)

 

[Hammerhance]

Welcome to the board.

That's a job for SUMIF
Try
=SUMIF('2013'!A:A,A5,'2013'!D:D)

Thank you Jonmo1. Usual case of over-complicating a simple calculation.

 

[Jonmo1]

You're welcome.

 

[Hammerhance]

When I'm sorting the rows the formula keeps the cell reference from the row it was originally on and doesn't change to the new cell e.g.

=SUMIF('2013'!B:B,Events!B2,'2013'!E:E) doesn't change no matter what row it is sorted to. I need it that number to be dynamic so it always looks at column B, but then changes the row number.

 

[Jonmo1]

I assume the formula is actually on the Events sheet?
Try removing that sheet name from the formula.

 

[Hammerhance]

I assume the formula is actually on the Events sheet?
Try removing that sheet name from the formula.

Your assumption didn't make an *** of you and Me and was spot on! thanks again.