Index formula is only returning the first value found in lookup

[RubiksCuber]

Hello there,

I am writing a spreadsheet that allows me to paste a performance report in the first tab, and it calculates the sum of all the rows for a particular agent. The problem I am having is that when an agent is listed twice, and i need the sum of both values returned to the second tab, it is only returning the first value found. I need it to be the sum of all that agents numbers...

An example is the agent in cell B17 on the callmart tab. Cell C17 should be 49 (which is the sum of cells B264 and B421 on the "paste performance here" tab) ... but it is only returning the value of B264.

Can anyone help?

[Fotis1991]

Hi there.

Give a try to this. In R4 and copy down and across.

=SUMPRODUCT(('PASTE PERFORMANCE HERE'!$A$6:$A$1000=$B4)*('PASTE PERFORMANCE HERE'!$B$4:$O$4=R$3)*('PASTE PERFORMANCE HERE'!$B$6:$O$1000))

[RubiksCuber]

perfect! you are awesome - thank you so much

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.