Sum of values for various occurrences of part numbers across tabs?!

[smilking]

Morning all,

Been a few months, but I have hit another stumbling block and would greatly appreciate some help in trying to overcome the problem. (apologies for the confusing title. . . couldnt think of a clear one!)

Please see attached file ‘sparessheet’ for a slice of the data that I am trying to manipulate. He goes the explanation.

The ‘May 2012’ tab is a report tab for accounts that displays all of the spare stock that we have on the ground and its associated value. This is sent to accounts monthly. The ‘master sheet’ tab is where the stock information is entered by the operations manager on the ground as and when he needs/orders/receives stock.
These two sheets were separate and the accounts tab was having to be populated manually each month by accounts, taking the data from the master sheet tab.
What I am trying to do is get it so that the May 2012 tab populates itself automatically as data is added to the master sheet.

E.G.

On the ‘may 2012’ tab, cell E70 needs to look at the part number in C70 and then match that number with all of the corresponding numbers in row E of the ‘Master sheet’ tab (but only when the supplier is UCL (mastersheet column C)). In all of the cases where C70 matches the number in a cell in column E, I would like the sum of all of the corresponding column F cells.
Row F is the same as above for any supplier other than UCL.

So far I initially looked at vlookup combined with sumproducts to no avail and I am not sure if this is the way to go?!

A fly in the ointment: you may notice that there are repeat occurrences of part numbers on the ‘May 2012’ tab. This needs to continue as these are different ‘batches’ for which different prices have been paid and this needs to be reflected by accounts. (BIG PAIN!)

Hopefully I have managed to make some sense out of it all and would greatly appreciate any help an guidance that can be given.

Many thanks in advance.

[Cutter]

Try

In E70: =SUMIFS('Master Sheet'!F:F,'Master Sheet'!C:C,"UCL",'Master Sheet'!E:E,C70) results in 15

In F70: =SUMIFS('Master Sheet'!F:F,'Master Sheet'!C:C,"<>UCL",'Master Sheet'!E:E,C70) results in 5

[smilking]

Hi Cutter,

Thank you very much for responding to me!

I like SUMIFS, did not think that what I was aiming for could be almost achieved so simply! I've had a play around with what you wrote and have managed to incorporate a couple of other criteria that narrows down what is displayed a bit further.

I will continue to play around with it and will hopefully get to the end that I am chasing.

For the meantime I will mark this as solved as hopefully I should be able to figure out the rest!

Once again, many thanks for your help!. . . What a resource this forum is. Must get a bit better at excel and then I can start helping others too.

[Cutter]

You're welcome. Thanks for the 'star tap'.