figure:Index/Match

[Tommy1005]

If you look at my attachment on the Supplier Competitiveness tab, I have highlighted the row yellow I am trying to populate.

Basically under "Supplier 1" I want it to look up in the Overview tab and see what part numbers Supplier 1 bid on. In this case its part numbers 1, 2, and 3. So then I need the formula to go to the Spend tab and sum the Annual Spend for part numbers 1, 2 and 3.

Another example is "Supplier 3" they quoted on all four part numbers in the Overview tab, so I need it to sum the all four part numbers in the Spend Data tab.

I have tried sumifs and I can't get it to work, maybe I'm over thinking it, but I was thinking an Index/Match might help..

Thanks for any help in advance? I attached the file.

[ConneXionLost]

Not sure if I'm following the logic, but try:

Please Login or Register  to view this content.

Cheers,

[Tommy1005]

That kind of helped..

But when I add a new supplier name like "Test" it doesn't work.. the total is "-"

I attached a more recent file.

EDIT: Under the tab 'Supplier Competitveness' it should be showing $83,878 for "Current Spend" under the Test supplier, but its not, its just showing "-" I don't get it.

[Tommy1005]

any one have any ideas? I'm totally stuck

[scottylad2]

Without putting any formulas into the latest sheet you posted, it's hard to work out anything

[martindwilson]

There is no sheet called 'Supplier Competitveness'

[Tommy1005]

Oh sorry, I re-uploaded the workbook, now it should make much more sense.

Look on the middle tab 'Supplier Competitiveness' and I've highlighted the cell yellow that I am having trouble with getting the proper answer.

[scottylad2]

Part number Annual Spend
035-2774Heil Environmental $4,482.00
054-2988-006Heil Environmental $8,715.00
054-2988-008Heil Environmental $4,092.00
054-2988-010Heil Environmental $24,257.97
22516Bradco $83,878.63





The above is all I'm seeing in your spend data tab but the formula you wish to evaluate is refering to a much bigger data set. If I'm not mistaken the sumif requires both the evaluation and sum range to be of equal dimensions, so i'm not sure how you're getting any answers in there at all

SUMIF(Overview!O$3:O$1500,">0",'Spend Data'!$C$3:$C$2001)

[martindwilson]

1 calculation is set to manual not auto so that will confuse things
2=SUMIF(Overview!P$3:P$1500,">0",'Spend Data'!$C$3:$C$2001) row 8 of
range Overview!P$3:P$1500 is greater than 0 it contains $55 thats your test column
the corresponding row in range 'Spend Data'!$C$3:$C$2001 = 0 hence your result
as pointed out 'Spend Data'!$C$3:$C$2001 is larger than Overview!P$3:P$1500 i dont think it matters but any calculation from p1501to p2001 just would not happen

[scottylad2]

Actually I stand corrected, Ranges don't have to be identical dimensions