# How to lookup multiple items add their corresponding values together and generate a percentage

I have a spreadsheet that I am working on that should average out some data into percentages.

I am trying to figure out what formula I should use to calculate this data.

I have two pivot tables, one that generates the quantity of defective parts, and one that generates the total quantity of parts received.

For example:

Lets say that I have 4 part numbers. `p/n-1`, `p/n-2`, `p/n-3` and `p/n-4`.

1,2, and 3 are from the same family, while 4 is from a different family.

Column A represents the part number.

Column B represents the defective quantity.

Column D represents the part number again.

Column E represents the total quantity received.

Column G represents the family (Fam-1 = P/N 1,2,3).

Column H represents the percentage of defective parts from that family: `(B1+B2+B3)/(E1+E2+E3)`

``````.  A         B     D         E     G       H
1  p/n-1     5     p/n-1     7     Fam-1   0.28
2  p/n-2     2     p/n-2     2     Fam-2   0.25
3  p/n-3     8     p/n-3     12
4  p/n-4     1     p/n-4     4
``````

I have 189 part numbers in total which I need to break down to their 28 respective families and calculate the defect percentage.

Currently I can do this manually by adding the quantities in each cell of the defective part number in relation to a family and dividing it by the total received, however this takes quite a bit of time.

What I would like to do is use something like this:

Lookup the specific part numbers in column A and add the quantity of each from column B.

Lookup those same numbers in column D and add the quantity of each one from column E.

Divide the quantity generated from column B into the quantity generated from column E to get the defective percentage.

As requested, here is an image of what I am trying to do.

https://i.imgur.com/x8d3Wt3.png

• Better you share the part of sheet as Screen shot along with expected results with us, will help us to find a better formula. – Rajesh S Sep 16 '18 at 7:35
• I think you should apply this formula to get the Rejection Rate,, `(Rejected/Total produced)x100`. Where `Total produce= (Rejected+Accepted)`. ☺ – Rajesh S Sep 16 '18 at 8:02
• I am using cell formatting which automatically appends the "x100" to give me the rejection rate that i need. – Morgan Sep 16 '18 at 16:30
• I also Added an image of what it looks like in Excel. – Morgan Sep 16 '18 at 16:57

Update

I kept tinkering around and also doing some research when I found a solution that worked for me.

I can use "SUMIF" to lookup and calculate the percentages that I need.

``````=(SUMIF(A1:A4,"p/n-1",B1:B4)
+SUMIF(A1:A4,"p/n-2",B1:B4)
+SUMIF(A1:A4,"p/n-3",B1:B4)
/
(SUMIF(D1:D4,"p/n-1",E1:E4)
+SUMIF(D1:D4,"p/n-2",E1:E4)
+SUMIF(D1:D4,"p/n-3",E1:E4))
``````

If anyone also knows a different way to go about this, please share as I would like to learn more about how to work efficiently in Excel.