CODEWITHSUNDEEP
excelexcel-formula
Rayne
Rayne

Reputation: 14977

Excel: Evaluate an expression if a cell in a range matches another cell

Say I have the table below.

Item  Count  All_Item  Total_Item  Percentage
A     4      A         10          40
B     6      B         20          30
E     2      C         20          0 
F     3      D         30          0
             E         10          20
             F         20          15

I want to calculate the last column "Percentage". What I would like to do, is to take the column "Item", check if the 1st entry equals "A" in "All_Item". If so, then take the corresponding Count/Total_Item*100, i.e. 4/10*100 = 40. If not, then look at the 2nd entry in "Item" and check if it equals "A" in "All_Item", and so on for all cells in "Item".

Then repeat the above for "B" in "All_Item", i.e. go through the cells in the column "Item" again and check if any one equals "B". If so, take the corresponding Count/Total_Item*100.

How can I do this in Excel?

Upvotes: 0

Views: 46

Answers (2)

Elnur Ibrahim-zade
Elnur Ibrahim-zade

Reputation: 821

I would recommed to you to use to tables. One include your data and another for percentage calculation.

Sheet1

  A    B       C          D
Item  Count  All_Item  Total_Item  
A     4      A         10          
B     6      B         20          
E     2      C         20          
F     3      D         30         
             E         10         
             F         20

Sheet2

      A       B
  1  Item  Precentage 
  2  A       =VLOOKUP(Sheet1!A:B,2,0)/VLOOKUP(A2,Sheet1!C:D,2,0)*100
  3  B     
  4  E     
  5  F

Note: This formula will work until you DO NOT HAVE duplicate items Like two "A"s in Item or All_Item column. When copying formula change sheet names.

Upvotes: 1

Jordan
Jordan

Reputation: 4514

Try something like:

=(SUMIF($A$2:$A$7,$C2,$B$2:$B$7)/$D2)*100

Upvotes: 1

Related Questions