Multiply Formula Based Off Several Texts

[Treesh1994]

What I am looking to do is have an autofill on A18 on down based on the number of vehicles from AT1. In this example I chose 6. I prefilled the information in on column A QTY section.

The High lighted words on H26, H30, H31 needs to have a total of 2 per vehicle. The rest of the lines in column H needs to be 1 per vehicle.

The words in H26, H30, H31 may be moved throughout the document so I want a formula that it doesn't matter where it is on the page. If the word ELUC3H010D, EMPS1STS4RBW, or EMPS2QMS5RBW shows up in the document, I want excel to know it needs to be multiplied by 2, and the rest by 1.


I hope this makes sense and someone can help me make a formula!

EDIT:
Also I forgot to mention, that I don't want to use VBA if at all possible

 

[Peter_SSs]

Welcome to the MrExcel board!

It is hard to see exactly what data is in what columns. For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with & it will clearly show the columns & rows.

So, you may have to adjust the columns, but does this help?

Treesh1994.xlsm
	A							H	I	J	K																																			AT
1																																														6
17
18	6								a
19	12								ELUC3H010D
20	6								c
21	6								d
22	12								EMPS2QMS5RBW
23	6								f
24	6								g
25	6								h
26	12								EMPS1STS4RBW
27	12								EMPS1STS4RBW
28	6								k
29	6								l
30	6								m
31	12								EMPS2QMS5RBW
Sheet1

Cell Formulas
Range	Formula
A18:A31	A18	=AT$1*(1+OR(I18={"ELUC3H010D","EMPS1STS4RBW","EMPS2QMS5RBW"}))

 

[Treesh1994]

=AT$1*(1+OR(I18={"ELUC3H010D","EMPS1STS4RBW","EMPS2QMS5RBW"}))

Pete,

Thank you for that information!

I will keep that in mind next time I need to post regarding the XL2BB!

When I plug that formula into the spreadsheet, it only multiplies everything x1. Was it working on your end when you recreated it?

 

[Peter_SSs]

Was it working on your end when you recreated it?

Yes, you can see it in A18:A31 of my mini sheet.

Start a fresh worksheet
Copy my mini sheet using this icon at the top left:

Select A1 of your blank fresh worksheet and Paste and see what happens

 

[Treesh1994]

Yes, you can see it in A18:A31 of my mini sheet.

Start a fresh worksheet
Copy my mini sheet using this icon at the top left: View attachment 97301

Select A1 of your blank fresh worksheet and Paste and see what happens

After looking at the formula, I messed up on my end!

I do have it working now! Thank you for your help on this!

One more thing if you can help, If the column "H" that I showed in the first post is blank, can we have the QTY section (A18) blank as well instead of putting a number?

Thank you again for your help on this!

 

[Peter_SSs]

After looking at the formula, I messed up on my end!

I do have it working now!

Good news!

If the column "H" that I showed in the first post is blank, can we have the QTY section (A18) blank as well instead of putting a number?

As I said earlier, I can't tell what is column H from your image so I will assume that is what I had shown as column I in my mini sheet.

Is this what you meant?

Treesh1994.xlsm
	A						G	H	I	J	K																																			AT
1																																														6
17
18	6							a
19	12							ELUC3H010D
20
21	6							d
22	12							EMPS2QMS5RBW
23	6							f
24	6							g
25	6							h
26	12							EMPS1STS4RBW
27	12							EMPS1STS4RBW
28
29
30	6							m
31	12							EMPS2QMS5RBW
Sheet1

Cell Formulas
Range	Formula
A18:A31	A18	=IF(H18="","",AT$1*(1+OR(H18={"ELUC3H010D","EMPS1STS4RBW","EMPS2QMS5RBW"})))

 

[Treesh1994]

=IF(H18="","",AT$1*(1+OR(H18={"ELUC3H010D","EMPS1STS4RBW","EMPS2QMS5RBW"})))

Yes that is exactly what I am looking for!

Thank you so much for your help on this!

I do have one more question (I promise I am done after this lol)

I do have one option that we give money back to the customer.
99B is the option code that we will give back money lets say 100.00.
Is there a way you can add this to the formula above to show the QTY of -6 from the first example above?

Thank you so much for your help on this! It is greatly appreciated!

 

[Peter_SSs]

99B is the option code

I have no idea what an 'option code' is and cannot see it mentioned anywhere in your sample so I have no idea how or if the extra condition can be added to the formula.

 

[Treesh1994]

I have no idea what an 'option code' is and cannot see it mentioned anywhere in your sample so I have no idea how or if the extra condition can be added to the formula.

I am sorry I should have specified better.

Like the examples above with the ELUC3H010D, EMPS2STS5RBW, And EMPS2QMS5RBW, 99B is another option but this time instead of multiplying by 2, I need to show a negative 1 per vehicle.

I hope this is enough information, sorry for the confusion with this

 

[Peter_SSs]

Like this then?

Treesh1994.xlsm
	A						G	H	I	J	K																																			AT
1																																														6
17
18	6							a
19	12							ELUC3H010D
20
21	-6							99B
22	12							EMPS2QMS5RBW
Sheet1

Cell Formulas
Range	Formula
A18:A22	A18	=IF(H18="","",IF(H18="99B",-AT$1,AT$1*(1+OR(H18={"ELUC3H010D","EMPS1STS4RBW","EMPS2QMS5RBW"}))))