+ Reply to Thread
Results 1 to 10 of 10

Calculate Cost of Products Based On Multiple Criteria

  1. 11-08-2017, 11:06 AM #1
    DomLeRoy
    DomLeRoy is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Bristol
    MS-Off Ver
    Excel 2010
    Posts
    5

    Calculate Cost of Products Based On Multiple Criteria

    Hi All,

    I'm looking for some assistance to calculate a cost based on a set range of criteria. There are 4 products and 15 variations, each are discounted as quantities increase.

    I require a formula that takes the input of a single cell and using two other criteria, returns a total cost. The criteria required to select the correct price could be selected from a simple Data Validation drop down box.

    Example of the product grid, variations and input boxes below.

    Excel Forum Help Image.png

    Thank you.

    Dom.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-08-2017, 01:03 PM #2
    shivya
    shivya is offline
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Calculate Cost of Products Based On Multiple Criteria

    Try this -
    $M$10:$Q$10,OFFSET($L$11,MATCH($E$6,$B$12:$B$26,0),MATCH($B$6,$C$11:$G$11,0),1,1),IF($C$6=$R$10:$V$10,OFFSET($Q$11,MATCH($E$6,$B$12:$B$26,0),MATCH($B$6,$C$11:$G$11,0),1,1)))))

    Please find attachment
    Attached Files Attached Files
    Last edited by shivya; 11-08-2017 at 01:06 PM.
    To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
    Don't forget to Mention your desired result in the sheet..
    There should be sample data only, a lot of data creates confusion.

    Thanks & Regards

    Shivya
    http://excelvbatipsforbeginners.blogspot.in/
    Reply With QuoteRegister To Reply
  3. 11-08-2017, 01:56 PM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Calculate Cost of Products Based On Multiple Criteria

    Try

    =INDEX($C$12:$V$26,MATCH($E$6,$B$12:$B$26,0),MATCH($C$6,$C$10:$V$10,0)+MATCH($B$6,$C$11:$G$11,1)-1)
    Attached Files Attached Files
    Last edited by JohnTopley; 11-08-2017 at 02:31 PM.
    Reply With QuoteRegister To Reply
  4. 11-08-2017, 05:10 PM #4
    DomLeRoy
    DomLeRoy is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Bristol
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate Cost of Products Based On Multiple Criteria

    Hi John,

    This is fantastic thank you, really helpful! Is there a way of making the product quantity field a manual input?

    For example, if somebody were to put any number between 30 and 250, one price would be selected, and if they were to put any number between 250 and 500 the correct rate for that would be selected?

    Thanks again for your help,

    Dom.
    Reply With QuoteRegister To Reply
  5. 11-08-2017, 05:11 PM #5
    DomLeRoy
    DomLeRoy is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Bristol
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate Cost of Products Based On Multiple Criteria

    Thank you for your help, I really appreciate it.
    Reply With QuoteRegister To Reply
  6. 11-08-2017, 05:28 PM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Calculate Cost of Products Based On Multiple Criteria

    For Product Quantity just remove the Data Validation.
    Reply With QuoteRegister To Reply
  7. 11-08-2017, 05:42 PM #7
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Calculate Cost of Products Based On Multiple Criteria

    You also need to change the product ranges in row 11 to

    0 31 251 501 751
    Attached Files Attached Files
    Last edited by JohnTopley; 11-08-2017 at 05:44 PM.
    Reply With QuoteRegister To Reply
  8. 11-09-2017, 05:13 AM #8
    DomLeRoy
    DomLeRoy is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Bristol
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate Cost of Products Based On Multiple Criteria

    Fantastic thank you!
    Reply With QuoteRegister To Reply
  9. 11-09-2017, 05:24 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculate Cost of Products Based On Multiple Criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  10. 11-09-2017, 05:26 AM #10
    DomLeRoy
    DomLeRoy is offline
    Registered User
    Join Date
    11-08-2017
    Location
    Bristol
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate Cost of Products Based On Multiple Criteria

    Thanks AliGW, have done that now.

    Dom.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Want to calculate breakeven for multiple products
    By kikola123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2017, 12:43 PM
  2. Counting products based on Invoice Number and Multiple Products on same column
    By a1mobile in forum Office 365
    Replies: 8
    Last Post: 07-01-2016, 02:07 AM
  3. [SOLVED] Need assistance with creating a multiple criteria formula to calculate cost
    By robiton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2016, 12:00 PM
  4. [SOLVED] Multiple IF's to calculate price based on cost and add handling charge and round price
    By RoyRose in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-13-2014, 01:18 PM
  5. Calculate weighted average for multiple products on a monthly basis
    By arvadata in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-08-2013, 12:07 PM
  6. Calculate Based On Multiple Criteria
    By Kumara_faith in forum Excel General
    Replies: 5
    Last Post: 02-03-2012, 04:06 PM
  7. Calculate based on multiple criteria
    By day92 in forum Excel General
    Replies: 2
    Last Post: 03-10-2011, 08:11 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1