Results 1 to 10 of 10

Thread: I need a formula please

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: I need a formula please

    Hi MarkoBrit,

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

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a formula please

    Quote Originally Posted by tyija1995 View Post
    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?

  4. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #5
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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
    Last edited by tyija1995; Mar 6th, 2019 at 06:45 AM.

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    New Member
    Join Date
    Mar 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #9
    New Member
    Join Date
    Mar 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a formula please

    Quote Originally Posted by jtakw View Post
    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. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: I need a formula please

    Then my formula should work fine.

    and Welcome to the forum, thanks for the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •