I need a formula please

[MarkoBrit]

I created a drop down list and used code to allow multiple choices of certain words in one cell. I need then to have a formula that can refer to a price based on these values and add the prices together.
So in B2 it may say "apples, oranges" it would then need to refer to a cell D4 that is 1 and D5 that is 2 and add them up to 3. if that makes sense.

 

[tyija1995]

Hi MarkoBrit,

Can you show more of the data you are working with please?

 

[MarkoBrit]

Hi MarkoBrit,

Can you show more of the data you are working with please?

I tried using this formula =IF(ISNUMBER(SEARCH("*Scissor Lift Trolley*",B2)),L2)
L2 has the price of the scissor lift trolley , but I also need to add into B2 "Gliders" and refer to L3 which is the price of the Gliders . Does that make sense?

 

[tyija1995]

So B2 is your validation which takes in combinations of different items?
Do you have a column for each individual item?
E.g. Col A would be
A1 - Gliders
A2 - Scissor Lift Trolley
A3 - Apples
A4 - Oranges
etc...
If that is the case you can put a column (say K)
with formula:
=IFERROR(SEARCH(A1,$B$2),"")
Which will populate with a number if the item is listed in the drop down, otherwise it will be left blank.
Then assuming column L is the prices, you could put in cell M1 a total price of drop down items as formula:
=SUMIF(K:K,">0",L:L)
If that makes sense?

 

[tyija1995]

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Individual Items:[/TD]
[TD]Items:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Match?[/TD]
[TD]Price:[/TD]
[TD]Total Price:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Gliders[/TD]
[TD]Scissor Lift Trolley, Gliders[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]5[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Scissor Lift Trolley[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With the following formulas:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Match?[/TD]
[TD]Price:[/TD]
[TD]Total Price:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=IFERROR(SEARCH(A2,$B$2),"")[/TD]
[TD]5[/TD]
[TD]=SUMIF(K2:K5,">0",L2:L5)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IFERROR(SEARCH(A3,$B$2),"")[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=IFERROR(SEARCH(A4,$B$2),"")[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=IFERROR(SEARCH(A5,$B$2),"")[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MarkoBrit]

[TABLE="width: 1210"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]A
Stores[/TD]
[TD]B
Items[/TD]
[TD] C
Total Cost [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]K
Item[/TD]
[TD]L
Price Each[/TD]
[TD]M
Quantity[/TD]
[/TR]
[TR]
[TD]Aberdeen[/TD]
[TD]Scissor Lift Trolley, Rollers, Gliders[/TD]
[TD] £ 750.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Scissor Lift Trolley[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Arnotts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gliders[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Belfast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Conveyor[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rollers[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Blanchardstown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kick Stool[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bristol[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Storage Straps/Chains[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Cardiff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Below is what I have I need Cell C2 to populate with the total cost of items from Cell B2

 

[MarkoBrit]

So as above I would need to have a column for each of the stores and I have 31 of them. What tyija1995 wrote is a great formula but would make a bit of a mess of the file as I was hoping to get everything on one Worksheet

 

[jtakw]

Hi,

If I understand correctly, but how do you handle different "quantities" for different stores ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Stores	Items	Total Cost								Item	Price Each	Quantity
2	Aberdeen	Scissor Lift Trolley, Rollers, Gliders	1170								Scissor Lift Trolley	750	1
3	Arnotts	Gliders, Kick Stool	45								Gliders	45	1
4	Belfast	Rollers, Gliders	420								Conveyor	360	0
5	Birmingham	Gliders, Conveyor	45								Rollers	375	1
6	Blanchardstown	Storage Straps/Chains	0								Kick Stool	25	0
7	Bristol		0								Storage Straps/Chains	15	0
8	Cardiff		0
Sheet628

Cell Formulas
Range	Formula
C2	=SUMPRODUCT((ISNUMBER(SEARCH(" "&K$2:K$7&","," "&B2&",")))*(L$2:L$7)*M$2:M$7)

 

[MarkoBrit]

Hi,

If I understand correctly, but how do you handle different "quantities" for different stores ?

	A	B	C	D	E	F	G	H	I	J	K	L	M
Stores	Items	Total Cost	Item	Price Each	Quantity
Aberdeen	Scissor Lift Trolley, Rollers, Gliders	Scissor Lift Trolley
Arnotts	Gliders, Kick Stool	Gliders
Belfast	Rollers, Gliders	Conveyor
Birmingham	Gliders, Conveyor	Rollers
Blanchardstown	Storage Straps/Chains	Kick Stool
Bristol	Storage Straps/Chains
Cardiff

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1170[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]750[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]420[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]375[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet628​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUMPRODUCT((ISNUMBER(SEARCH(" "&K$2:K$7&","," "&B2&",")))*(L$2:L$7)*M$2:M$7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Stores would only need 1 of each item if needed at all.

 

[jtakw]

Then my formula should work fine.

and Welcome to the forum, thanks for the feedback.