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))


[TABLE="width: 825"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Advanced Engineering[/TD]
[TD]NEC[/TD]
[TD]November[/TD]
[TD]£4,355[/TD]
[TD]£4,355[/TD]
[/TR]
[TR]
[TD]AP&M[/TD]
[TD]Olympia[/TD]
[TD]May[/TD]
[TD]£10,730[/TD]
[TD]£10,730[/TD]
[/TR]
[TR]
[TD]Autumn Furniture Show[/TD]
[TD]Stonleigh Park[/TD]
[TD]October[/TD]
[TD]£0[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]BETT[/TD]
[TD]Excel[/TD]
[TD]January[/TD]
[TD]£13,275[/TD]
[TD]£13,275[/TD]
[/TR]
[TR]
[TD]Biofuels Expo[/TD]
[TD]Stoneleigh[/TD]
[TD]September[/TD]
[TD]£3,920[/TD]
[TD]£2,985[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 637"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
This sheet is '2013'

[TABLE="width: 557"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Biofuels Expo[/TD]
[TD]Ocober[/TD]
[TD]2013[/TD]
[TD]£2,985[/TD]
[/TR]
[TR]
[TD]Biofuels Expo[/TD]
[TD]Ocober[/TD]
[TD]2013[/TD]
[TD]£935
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.