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.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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
648
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
648
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
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,146
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,146
Then my formula should work fine.

and Welcome to the forum, thanks for the feedback.
 

Forum statistics

Threads
1,082,131
Messages
5,363,337
Members
400,726
Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top