# Thread: I need a formula please Thanks:  1 Post #5238416 (1) Likes: 0

1. ## I need a formula please

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.

2. ## Re: I need a formula please

Hi MarkoBrit,

3. ## Re: I need a formula please

Originally Posted by tyija1995
Hi MarkoBrit,

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?

4. ## Re: I need a formula please

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?

5. ## Re: I need a formula please

 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

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

6. ## Re: I need a formula please

 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

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

7. ## Re: I need a formula please

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

8. ## Re: I need a formula please

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

Sheet628

Worksheet Formulas
CellFormula
C2=SUMPRODUCT((ISNUMBER(SEARCH(" "&K\$2:K\$7&","," "&B2&",")))*(L\$2:L\$7)*M\$2:M\$7)

9. ## Re: I need a formula please

Originally Posted by jtakw
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
Sheet628

Worksheet Formulas
Cell Formula
C2 =SUMPRODUCT((ISNUMBER(SEARCH(" "&K\$2:K\$7&","," "&B2&",")))*(L\$2:L\$7)*M\$2:M\$7)
Hi Stores would only need 1 of each item if needed at all.

10. ## Re: I need a formula please

Then my formula should work fine.

and Welcome to the forum, thanks for the feedback.