+ Reply to Thread
Results 1 to 6 of 6

Find the product based on multiple criteria

  1. #1
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    Find the product based on multiple criteria

    Hello, I am trying to create a database/cut list in Excel and I'm stuck on a particular function. I've added a sample database for reference.

    I have (3) worksheets creates so far:
    - Summary Sheet, which I want to summarize the data
    - Releases Sheet, which is a line-item list of all orders we need to fulfill
    - Item Info Sheet, which is a database of items including, part no., description & required hardware items

    You'll see that each order that's entered on the 'Releases' sheet has a 'Release No.' associated with it. If you now go to the 'Summary' sheet, you'll see that I've created a table where you can input a release number, and it will summarize the total number of cabinets & the dollar-amount total for that particular release number (input cells are not filled, output cells are filled in with yellow). The input section of that table has space for 4 entries, because there are some instances where there are two separate orders that are shipping to the same destination (in this example, release 8 & 18 are shipping to the same destination, so their data values for cabinet quantity and dollar amount are added together. I've entered each order number on a separate line item below to show the split between the two orders).

    For the purposes of this sample, I want to calculate how many Hinges are required for the orders that are entered in the 'Summary' worksheet. If you look at the 'Item Info' sheet, you'll see that Item no. 1299B10012 requires 1 pair of Hinges, Item no. 1299B30039 requires 2 pairs of Hinges, etc. The quantity of Hinges required is listed in column I of the 'Item Info' sheet.

    Release no. 2 has an order for (40) pieces of 1299B10015, (75) pieces of 1299B10018, (40) pcs of 1299B10021, etc. If you do the math manually, the total number of hinge pairs required to complete the orders in Release No. 2 is 495. if you now do the math for all of the Release No's entered into the 'Summary Sheet' form (2, 8, 18, 8, 18), the total number of hinge pairs required will be 1135 pcs. I've entered this number manually on the 'Summary' sheet in bold (in the 'Material's' section, next to the 'Hinges' label). I need to come up with a formula that will match the Release Number, Part Number, Part Quantity & Required Hinges per Part in order to get to this number.

    This is a problem that is quite a bit more complex than I've ever encountered in Excel. Any help would be appreciated.

    Thank you,
    James
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    2016
    Posts
    511

    Re: Find the product based on multiple criteria

    Hi JV, probably not what you were hoping for but I got it to work by extending the Materials list onto the Releases sheet and adding up from there.
    Those columns could be hidden of course.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    633

    Re: Find the product based on multiple criteria

    Summary Sheet

    G5=SUMPRODUCT(SUMIF(Releases[Release No.],Ship_Summary[@[Column1]:[Column4]],Releases[Quantity]))

    Copy down

    H5=SUMPRODUCT((ISNUMBER(MATCH(Releases[Release No.],Ship_Summary[@[Column1]:[Column4]],0)))*(Releases[Quantity])*(Releases[Price]))

    Copy down

    H29=SUMPRODUCT(SUMIFS('Releases'!AI2:AI51,Releases[Release No.],Ship_Summary[[Column1]:[Column4]]))

  4. #4
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    Re: Find the product based on multiple criteria

    Hello, and happy new year. I had an earlier version of this sheet setup in that fashion. it works, but it made my sheet way too long. I was hoping to find a more efficient way through a formula. Thank you for your help

  5. #5
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    Re: Find the product based on multiple criteria

    Hi Caracalla, the formula for H29 seems to be on the right track, but I'm a bit confused. How do i tie it in with the "Items" tab in order to calculate the number of hinges? Also, your formula references cells AI12:AI51 in the 'Releases' tab, but there is no data in that cell range.

  6. #6
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    Re: Find the product based on multiple criteria

    Hi again Caracalla, I think you created your formula based on the amended sheet example that Beamernsw made. Please see my original sheet, and let me know if you think a formula would work based on that. thanks.

+ 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: 5
    Last Post: 05-24-2019, 04:48 PM
  2. [SOLVED] Sum product based on Multiple Criteria
    By Barieq in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-24-2018, 02:07 AM
  3. Replies: 6
    Last Post: 02-21-2016, 04:15 AM
  4. [SOLVED] find row based on multiple criteria
    By pugulis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2016, 05:41 PM
  5. Replies: 3
    Last Post: 08-21-2013, 09:59 AM
  6. [SOLVED] Find Product Details in Another Sheet based on Product ID and Copy some Fields From there
    By kevalkothari in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2012, 10:43 AM
  7. multiple column sum product based on set criteria
    By b16dlg in forum Excel General
    Replies: 4
    Last Post: 07-11-2012, 07:51 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