Index and Match Formula does not sum up...

[Porterrob]

http://www.box.net/shared/88vj8y987u

Hi.

I have an index and match formula on the workbook above, however I need the formula so that it will add up the total value.

For Example.

On the consignment stock sheet in Y45, it says 5000 as it has match up the value of 92979 on the Stock out sheet with that corresponding part. However it should say 6000 as there is a second column adjacent on the stock out with the same PO number 92979 and 1000pcs of that part.

Any ideas on how to get it to do this?

Thanks

 

[Andrew Poulsom]

Try:

=SUMIF('Stock Out'!$3:$3,Y$3,INDEX('Stock Out'!$1:$100,MATCH($A45,'Stock Out'!$A:$A,FALSE),0))

 

[Porterrob]

Thanks for your help,

It comes across an error (Inconsistent formula) plus when I try to amend it for the other cells it doesn't work and comes up with a N/A error.

Any ideas?

 

[Andrew Poulsom]

If I put that formula in Y45 it returns 6000 and I can't duplicate the #N/A error. In case you don't have a part number match:

=IF(ISNUMBER(MATCH($A45,'Stock Out'!$A:$A,FALSE)),SUMIF('Stock Out'!$3:$3,Y$3,INDEX('Stock Out'!$1:$100,MATCH($A45,'Stock Out'!$A:$A,FALSE),0)),0)