# Find the product based on multiple criteria

1. ## 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

2. ## 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.

3. ## 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. ## 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. ## 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. ## 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.

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