Sum of results from Index match formula

**TomJones0505** · 06-19-2009, 08:36 AM

Hi all,

I'm a relative novice with excel and I have encountered a problem that I can't seem to get past.

I've managed to get it to find a value using the formula below, which is correct but does not show an accumulative figure when the criteria is met again further down the index area in spreadsheet 1.

The formula that I have at the moment is;

{=INDEX(sheet1!C:C,MATCH(sheet2!B325,IF(sheet1!A:A=sheet2!A325,sheet1!B:B),0))}

Which returns the first value it finds matching the criteria, but I would like it to add the first value it finds to the second and so on.

Please help me.

**DonkeyOte** · 06-19-2009, 08:40 AM

Use a SUMPROUCT pehaps ?

=SUMPRODUCT(--(Sheet1!$A$:A$100=Sheet2!A325),--(Sheet1!$B$1:$B$100=Sheet2!B325),Sheet1!$C$1:$C$100)

Note i altered the ranges to rows 1:100 -- pre XL2007 you can't use entire column references in Sumproduct and you should keep ranges to a minimum else performance will be affected, moreover if you have XL2007 you can use SUMIFS which is preferable to Sumproduct.

=SUMIFS(Sheet1!$C$1:$C$100,Sheet1!$A$1:$A$100,Sheet2!A325,Sheet1!$B$1:$B$100,Sheet2!B325)

Alternatively think of using a Pivot Table...

**NBVC** · 06-19-2009, 08:42 AM

This should work

=Sumif(sheet1!B:B,sheet2!B325,sheet1!C:C)

Although not to sure what you are trying to do here?

MATCH(sheet2!B325,IF(sheet1!A:A=sheet2!A325,sheet1!B:B),0)

**TomJones0505** · 06-19-2009, 09:12 AM

Not sure if those formula work with my example.

Eg

I want to match Col A and Col B in sheet1 with two criteria in sheet2. If the value in Col A and B match I would then like to have the value from Col C in sheet1. The formula I provided does that. But if the Col A and B match more than once with the two values in sheet2, it will only show the first value from Col C and ignore the rest. I would like it to add them together.

You must try to remember that I'm a total clot.

**NBVC** · 06-19-2009, 09:19 AM

You mean?

=SUMPRODUCT(--(Sheet1!$A$:A$100=Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100)

**TomJones0505** · 06-19-2009, 09:23 AM

sorry. Ignore that. The sumIF wins the day. Thank you very much.

**NBVC** · 06-19-2009, 09:49 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save