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:D$6)/SUMIF(A$1:A$6,A8,C$1:C$6)

"SteveFox" <SteveFox.1yvl1m_1132624502.6058@excelforum-nospam.com> wrote in
message news:SteveFox.1yvl1m_1132624502.6058@excelforum-nospam.com...
>
> 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.
>
>
> --
> SteveFox
> ------------------------------------------------------------------------
> SteveFox's Profile:
> http://www.excelforum.com/member.php...o&userid=28975
> View this thread: http://www.excelforum.com/showthread...hreadid=487078
>