+ Reply to Thread
Results 1 to 8 of 8

conditional multiplication

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    California
    MS-Off Ver
    0ffice 360
    Posts
    4

    conditional multiplication

    On sheet 1, I have the "product" and "units sold". On sheet 2 I have "product" and "product cost". For every row on sheet 1, I need to calculate "cost of goods" by comparing the product on sheet 1 to product on sheet 2 and matching the price of the product and multiplying with the 'units sold'. Please see table below. Highly appreciate any help offered to solve this conditional multiplication. Thanks

    Sheet 1
    "Product" "Units Sold" "Cost of Goods"
    Item 1 ----- 500
    Item 2 ----- 409
    Item 1 ----- 678
    Item 4 ----- 879
    Item 2 ----- 98
    Item 6 ----- 23
    Item 9 ----- 665
    Item 8 ----- 76
    Item 1 ----- 90
    Item 4 ----- 532

    Sheet 2
    Product Product Cost
    Item 1 ----- $4.00
    Item 2 ----- $5.00
    Item 3 ----- $3.00
    Item 4 ----- $2.00
    Item 5 ----- $1.00
    Item 6 ----- $8.00
    Item 7 ----- $6.00
    Item 8 ----- $9.00
    Item 9 ----- $34.00
    Item 10 ----- $56.00

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,041

    Re: conditional multiplication

    Use VLOOKUP to get the relevant cost and multiply by the quantity.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    California
    MS-Off Ver
    0ffice 360
    Posts
    4

    Re: conditional multiplication

    Thank you. it works
    Last edited by jsm250; 05-15-2019 at 06:44 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,041

    Re: conditional multiplication

    Needs to refer to two columns to get the second one.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-15-2019
    Location
    California
    MS-Off Ver
    0ffice 360
    Posts
    4

    Re: conditional multiplication

    Awesome, thank you

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,041

    Re: conditional multiplication

    And you need to make the range absolute.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,041

    Re: conditional multiplication

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  8. #8
    Registered User
    Join Date
    05-15-2019
    Location
    California
    MS-Off Ver
    0ffice 360
    Posts
    4

    Re: conditional multiplication

    Got it, you saved my day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1