Lookup with SUMIF Problem

[Zanith]

Hello all,

I've been scratching my head over a problem with Excel and I'm hoping one of you can be of assistance.

I have a spreadsheet with two worksheets in it.

Sheet 1 - Data - Contains a dump of data that I'm wanting to do analysis on.
Sheet 2 - Lookup - This sheet is used to extract information from the Data sheet.

What I'm trying, and failing to find the proper way to do it, is on the Lookup sheet have it parse Column A of Data for a specific value. Once found I want it to perform a SUMIF statement across the ROW in which the lookup found a result. I have the SUMIF working the way I want it (see in attached example sheet) what I can't tackle is how to get it to perform this lookup on the row I'm searching for.

Any help would be appreciated. Thanks in advance!

[Zanith]

A little more detailed explanation on my problem.

On the Lookup worksheet my problem is directly related to the formula located in columns C. What I'm trying to accomplish with cell C2 is a lookup of the value in A2 (PICKLES) within column A of the Data worksheet. Once A2 is found, I would like the formula that is currently in C2 in the example spreadsheet to execute, which will extract only the relevant data in that row for PICKLES.

I'm at a loss on how to move forward with this and I appreciate any assistance given.

Thanks!

[benishiryo]

maybe this for C2:
=SUMIF(Table1[KIT NAME],Table2[[#This Row],[KIT NAME]],Table1[PART COST 1])

and this for D2:
=SUMIF(Table1[KIT NAME],Table2[[#This Row],[KIT NAME]],Table1[PART COST 2])

or if cell references make more sense to you (range will still be dynamic when it increases, don't worry):
=SUMIF(Data!$A$2:$A$5,A2,Data!$F$2:$F$5)
=SUMIF(Data!$A$2:$A$5,A2,Data!$J$2:$J$5)

but do note that SUMIF will sum up all values if say "Pickles" appear twice. not sure if that's what you want. if you just want to return the 1st line of pickles, then:
=VLOOKUP(A2,Table1[[KIT NAME]:[PART COST 1]],6,0)
=VLOOKUP(A2,Table1,10,0)

or with cell references:
=VLOOKUP(A2,Data!$A$2:$F$5,6,0)
=VLOOKUP(A2,Data!$A$2:$J$5,10,0)

[Zanith]

Thanks benishiryo!

There is an additional wrinkle in this formula which is making it difficult for me to apply your solutions.

The SUMIF formula I'm using in Column C (MFG COSTS) is as follows:

=SUMIF(Table1[@[PART TYPE 1]]:Table1[@[PART TYPE 2]],"MFG",Table1[@[PART COST 1]]:Table1[@[PART COST 2]])

The SUMIF formula I'm using in Column D (BUY COSTS) is as follows:

=SUMIF(Table1[@[PART TYPE 1]]:Table1[@[PART TYPE 2]],"BUY",Table1[@[PART COST 1]]:Table1[@[PART COST 2]])

This is important as the ROW that I'm doing the look-up on, let's say STRAWBERRIES in the example sheet, has cost information that falls into a MFG or BUY category. I need to separately search for these costs and return MFG costs into column C and BUY costs into column D. The reason is I handle these costs different depending on if I make them (MFG) or buy them from a vendor (BUY).

In my actual spreadsheet the part costs can range from PART COST 1 to PART COST 200.

So, to summarize:

Column C (MFG COSTS) - needs to look ONLY at the costs which have a PART TYPE of "MFG" and then sum them.
Column D (BUY COSTS) - needs to look ONLY at the costs which have a PART TYPE of "BUY" and then sum them.

Thank you very much for your continued help, I can see it getting closer