I need a formula please

MarkoBrit

New Member
Joined
Mar 6, 2019
Messages
11
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Hi MarkoBrit,

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

MarkoBrit

New Member
Joined
Mar 6, 2019
Messages
11
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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ABCDEFGHIJKLM
1Individual Items:Items:Match?Price:Total Price:
2GlidersScissor Lift Trolley, Gliders23511
3Scissor Lift Trolley16
4Apples3
5Oranges2

<tbody>
</tbody>

With the following formulas:

KLM
1Match?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>
 
Last edited:

MarkoBrit

New Member
Joined
Mar 6, 2019
Messages
11
A
Stores
B
Items
C
Total Cost
K
Item
L
Price Each
M
Quantity
AberdeenScissor Lift Trolley, Rollers, Gliders £ 750.00Scissor Lift Trolley7501
ArnottsGliders451
BelfastConveyor3600
BirminghamRollers3751
BlanchardstownKick Stool250
BristolStorage Straps/Chains150
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

New Member
Joined
Mar 6, 2019
Messages
11

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,341
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 6, 2019
Messages
11
Hi,

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

ABCDEFGHIJKLM
1StoresItemsTotal CostItemPrice EachQuantity
2AberdeenScissor Lift Trolley, Rollers, Gliders1170Scissor Lift Trolley7501
3ArnottsGliders, Kick Stool45Gliders451
4BelfastRollers, Gliders420Conveyor3600
5BirminghamGliders, Conveyor45Rollers3751
6BlanchardstownStorage Straps/Chains0Kick Stool250
7Bristol0Storage Straps/Chains150
8Cardiff0

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,341
Office Version
  1. 2016
Platform
  1. Windows
Then my formula should work fine.

and Welcome to the forum, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top