I need a formula please

MarkoBrit

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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi MarkoBrit,

Can you show more of the data you are working with please?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi,

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


Book1
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
Sheet628
Cell Formulas
RangeFormula
C2=SUMPRODUCT((ISNUMBER(SEARCH(" "&K$2:K$7&","," "&B2&",")))*(L$2:L$7)*M$2:M$7)
 
Upvote 0
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.
 
Upvote 0
Then my formula should work fine.

and Welcome to the forum, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top