Excel formula help needed

[SteveFox]

Could someone tell me the excel formula to do the below?

If the 1st word (in cell A1) is the same as the 1st word of the 2nd row
(A2) then add the 3rd word of the 1st row (1C) and the 3rd word of the
2nd row (2C) and divide the sum of the 4th word of the 1st and 2nd row
(D1 and D2) and put the result into a cell with only the 1st word and
result of the division. And I want this to loop until it reachs the
last row.

e.g. I have a file which contains 6 rows below:

AAA Unit1 60 39
AAA Unit7 30 15
BBB Unit3 80 60
CCC Unit4 50 25
CCC Unit8 90 45
CCC Unit9 70 40

I want it to output 3 row below:

AAA 0.6
BBB 0.75
CCC 0.52


The result for AAA 0.6 was calculated by (39+15)/(60+30) taken from 1st
and second row.


Any help will be greatly appreciated.

 

[Barb Reinhardt]

Let's say your six rows are in A1-D6

A9 = "AAA"
A10 = "BBB"
A11 = "CCC"

Try this
B9 =SUMIF(A$1:A$6,A8,D$1$6)/SUMIF(A$1:A$6,A8,C$1:C$6)

 

[SteveFox]

Thanks Barb!