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]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Individual Items:

Items:

Match?

Price:

Total Price:

2

Gliders

Scissor Lift Trolley, Gliders

23

5

11

3

Scissor Lift Trolley

1

6

4

Apples

3

5

Oranges

2

<tbody>
</tbody>

With the following formulas:

	K	L	M
1	Match?	Price:	Total Price:
2	=IFERROR(SEARCH(A2,$B$2),"")	5	=SUMIF(K2:K5,">0",L2:L5)
3	=IFERROR(SEARCH(A3,$B$2),"")	6
4	=IFERROR(SEARCH(A4,$B$2),"")	3
5	=IFERROR(SEARCH(A5,$B$2),"")	2

<tbody>
</tbody>

 

[MarkoBrit]

A Stores

B Items

C Total Cost

K Item

L Price Each

M Quantity

Aberdeen

Scissor Lift Trolley, Rollers, Gliders

£ 750.00

Scissor Lift Trolley

750

1

Arnotts

Gliders

45

1

Belfast

Conveyor

360

0

Birmingham

Rollers

375

1

Blanchardstown

Kick Stool

25

0

Bristol

Storage Straps/Chains

15

0

Cardiff

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

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 ?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Stores</td><td style=";">Items</td><td style=";">Total Cost</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Item</td><td style=";">Price Each</td><td style=";">Quantity</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Aberdeen</td><td style=";">Scissor Lift Trolley, Rollers, Gliders</td><td style="text-align: right;;">1170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Scissor Lift Trolley</td><td style="text-align: right;;">750</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Arnotts</td><td style=";">Gliders, Kick Stool</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gliders</td><td style="text-align: right;;">45</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Belfast</td><td style=";">Rollers, Gliders</td><td style="text-align: right;;">420</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Conveyor</td><td style="text-align: right;;">360</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Birmingham</td><td style=";">Gliders, Conveyor</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Rollers</td><td style="text-align: right;;">375</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Blanchardstown</td><td style=";">Storage Straps/Chains</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Kick Stool</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bristol</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Storage Straps/Chains</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Cardiff</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet628</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">" "&K$2:K$7&","," "&B2&","</font>)</font>)</font>)*(<font color="Red">L$2:L$7</font>)*M$2:M$7</font>)</td></tr></tbody></table></td></tr></table><br />

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet628​

Worksheet Formulas Cell Formula C2 =SUMPRODUCT((ISNUMBER(SEARCH(" "&K$2:K$7&","," "&B2&",")))*(L$2:L$7)*M$2:M$7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.