+ Reply to Thread
Results 1 to 3 of 3

SUMIF CEILING formula to work out total price ROUNDUP

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    SUMIF CEILING formula to work out total price ROUNDUP

    I need a formula that can work out the total price an item will cost me based on - the qty i require, the package sizes the item comes in and the minimum order qty.

    So, i may need to buy 50 x item but the package size available is 20 item per package and the minimum buy is 20 x item so i need a formula to work out the total that works out, if the item qty required is more than MOQ and fits the package size then times the unit price by the qty required.
    but if the qty required is less than MOQ then times unit price by MOQ
    and if the qty required is more than MOQ but doesnt match the package size then it would have to work out the next round up from package size and times that by unit price

    so the above example total price would be the unit price x 60 as the qty required is 50 but the pack size is 20 so the next amount up in that pack size would be 60 (3x packs of 20) and 50 is greater than the MOQ (being 20)

    and i cannot do this manually as i have hundreds of rows of data across multiple worksheets

    I've found a few suggestions elsewhere, being IF, COUNTIF, SUMIF, CEILING formulas etc but nothing seems to take everything into account and i have no idea how to put anything together myself

    attached example:
    Example.xlsx
    Last edited by aimeecrystalaid; 09-05-2013 at 11:57 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: SUMIF CEILING formula to work out total price ROUNDUP

    Hi,

    In cell E2, enter this formula and drag it down as far as is necessary:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope this helps

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: SUMIF CEILING formula to work out total price ROUNDUP

    brilliant! that works perfectly across my example sheets. i'll give it a test run on some of my larger sheets now and see how it goes

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  2. Replies: 2
    Last Post: 02-03-2013, 11:07 AM
  3. formula for unit price with minimum amount
    By yvanblanchette in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2008, 11:55 PM
  4. Summing Total Sales, Based on Quantity & Price
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-28-2005, 11:08 AM
  5. How to calcuale a selling price based on a minimum gross margin
    By dougie8rown in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-10-2005, 08:57 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1