SUMIF? 1

[mfroggie]

I need to create a formula that will go thru a cell and find a character that matches my criteria and pull the price for that criteria to create a base price for the item in the initial cell.
For example I have an item on Sheet1
ABC1200.124
ABC1200.135

I have options on a Sheet2
1 1.00
2 2.00
3 3.00
4 4.00
5 5.00
I need to be able search the cell with ABC1200.124 and pull out the price from sheet2 for the 1+2+4 or the 1+3+5 that are after the decimal and sum them. Those are the options that will be added to the base price of the item ABC1200. I want to create a formula that I can paste into each cell down a column so it will search thru a list of items I export out of the database. Let it pull those prices from sheet2 to come up with my new price and then import back in to the database. I was looking at SUMIF but I am not sure that is the best solution.

Thanks,
mfroggie


Mfroggie

 

[Gavona]

First I would insert a column beside your existing and use Data,Text to columns to separate out the bits before and after the decimal point.

Then you could use sumif.

Alternatively a pivot table could then give you the sum of the prices for each item.

Gavin

 

[SkipVought]

Hi,

OK you search for ABC1200.124 on sheet1 and find it. What is the added value of this excersize? SO WHAT! What have you achieved? Having FOUND it, we have not discovered anything, except that it is there.

pull out the price from sheet2

Where is the PRICE on sheet2? All you have displayed is OPTIONS.

This is ALL very unclear. It might makes sence to you, but it is unintelligible to me.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mfroggie]

Ok, let me see if I can explain it better.

You have base item abc1200 and it sells for 20.00. Now you have options you can add to and they are listed on sheet2 with their identifier in column a and their price in column b. We will say 1 thru 5 and to make it simple we will say option 1 cost's 1.00, option 2 costs 2.00, option 3 costs 3.00, etc. So an item that includes the base item abc1200 and a customer wants to add option 1, option 2 and option 4. The item for the customer would be abc1200.124.
In column a on sheet1 will have all the different items number (all the different combinations of base items and options that have been exported out of the database) and say column c I want a formula that will go to column A (or I will break that out into column B as suggested) and look to see if there is a 1 or a 2 or a 3 or a 4 or a 5 in the last few positions that would be the options, then go to sheet2 for the options it finds and pull back to sheet1 column c the sum of those options. In my example the sum would be 7.00 since 1 is 1.00, 2 is 2.00 and 4 is 4.00. So that I only have to have 1 formula and can paste it into column C on sheet1 for all the items that are in column A. I hope this makes a little more sense because I am not always good at explaining.

Thanks,

mfroggie

Mfroggie

 

[SkipVought]

Please post a coordinated example of data on sheet1 and sheet2 showing ALL the data values that one would need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mfroggie]

Sure, I hope this is what you want.

Sheet1
ColA ColB ColC(results)
abc1200.124 124 7.00
abc1200.135 135 9.00

Sheet2
ColA ColB
1 1.00
2 2.00
3 3.00
4 4.00
5 5.00

So as per Gavins suggestion, I break the options portion of the part number out and have the formula check ColB on sheet 1 for what option codes are there then go over to Sheet to to calculate the cost of the options and sum them and deposit that sum in ColC of Sheet1.

Thanks,

mfroggie

Mfroggie

 

[Gavona]

Let me see if I understand:
1. In Sheet1 colB you have options? Will there always be three? If not what is the maximum number?

2. The options are in Sheet2, colA with the cost in sheet2, colB?

3. In your example the cost of each option is the same as the option number - I assume this is just to confuse us and in fact, for example, option2 might not cost $2?


If I have it right then further split Sheet1,colB into separate cells for each option (Data, Text to Columns). Then use a series of vlookups to sheet2 to get the costs. (Though there is probably a smarter way to do it)

Gavin

 

[mfroggie]

1. There will not always be 3 there could be up to 7 I believe. (Clients data, I am transferring it now to look at it.)

2. Yes

3. I was trying to make it simple the costs could be anything.

Thanks,

mfroggie

Mfroggie

 

[SkipVought]

Your lookup is
[tt]
Sheet1!C1:
=INDEX(Sheet2!B:B,MATCH(VALUE(MID(B1,1,1)),Sheet2!A:A,0),1)+INDEX(Sheet2!B:B,MATCH(VALUE(MID(B1,2,1)),Sheet2!A:A,0),1)+INDEX(Sheet2!B:B,MATCH(VALUE(MID(B1,3,1)),Sheet2!A:A,0),1)

[/tt]
I got 7.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mfroggie]

Embedded formula like that is what I was thinking. I am transferring their data now and tomorrow will load it into my software and run some tests to see how that works. Would be great to have that so everytime they want to do a new price increase all I have to do is export their items and paste the formula into the spreadsheet.

Thanks so much. I will let you know how it works or if I have anyother questions.

mfroggie

Mfroggie

 

[mfroggie]

This did work. I will have to tweak the Mid portion a bit for different rows since some items are different lengths but the formula is good. Thank you for your help Skip.

mfroggie

Mfroggie