sum index match returning first value not adding

[each]

I am tyring unsucessfully to add up some values in an array - the formula below is simply returning the first value. Can anyone see where I have gone wrong please.

=SUM(INDEX('nominal apportionments'!B96:J432,MATCH(B95:C95,'nominal apportionments'!B96:B432),9))

What I want to do is add all the values in column 9 (J) where the values in column B equal the values in the range b95 to c95 on the current sheet.

Thanks

E

[DonkeyOte]

Couple of options spring to mind immediately:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[sweep]

DonkeyOte,

Why not

=SUMPRODUCT(('nominal apportionments'!$B$96:$B$432=$C95:$D95)*('nominalapportionments'!$J$96:$J$432))

[DonkeyOte]

That too if you can be sure the latter range will always contain numerics.

[each]

thanks for your help - however both the sumproduct formulas only return the second value, they do not add up the two values.

Donkey - your first formula does work but in reality my range for input criteria b96 to c 96 could be much longer and varialble number of cells - so something that can deal with this would be better.

[DonkeyOte]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

There's no reason why the SUMPRODUCT would fail to return the correct result as I see it...

[each]

My mistake - copied forumla into wrong columns. Both sumproducts do work.

Can anyone advise why my sum index match formula did not work?

thanks

[daddylonglegs]

Can anyone advise why my sum index match formula did not work?

MATCH returns an array but, except in some convoluted circumstances, INDEX does not so you can't use that form to do what you require......