SUM
 

Hi,

I have a few hundred rows. Each row contains a person information and
amount. There are multiple rows with the same person, but different amount.
I want to create a formula in each row on a separate column (D) that only
adds the amount for the same person. I do not want to use subtotal because
it adds an additional row without retaining the person info. In column D the
formula only shows a zero on each row if there are multiple amounts with the
same person. The last row should be the sum of all the amounts from the same
person. I hope this makes sense.

(A) (B) (C) (D) (E)
(1) 123 John Doe 24.16 0 Address
(2) 123 John Doe 16.45 40.61 Address
(3) 452 Sarah Jane 54.56 0 Address
(4) 452 Sarah Jane 45.45 0 Address
(5) 452 Sarah Jane 10.12 110.13 Address
(6) 564 Billy the Kid 65.45 0 Address
(7) 564 Billy the Kid 45.60 0 Address
(8) 564 Billy the Kid 65.20 176.25 Address

SUM
 

hi
sounds like you could use sumif.

=sumif(name range, name, amount range)
or
=sumif(B1:B*,"john doe", C1:C8)

regards
FSt1

"Talladega" wrote:

Hi,

I have a few hundred rows. Each row contains a person information and
amount. There are multiple rows with the same person, but different amount.
I want to create a formula in each row on a separate column (D) that only
adds the amount for the same person. I do not want to use subtotal because
it adds an additional row without retaining the person info. In column D the
formula only shows a zero on each row if there are multiple amounts with the
same person. The last row should be the sum of all the amounts from the same
person. I hope this makes sense.

(A) (B) (C) (D) (E)
(1) 123 John Doe 24.16 0 Address
(2) 123 John Doe 16.45 40.61 Address
(3) 452 Sarah Jane 54.56 0 Address
(4) 452 Sarah Jane 45.45 0 Address
(5) 452 Sarah Jane 10.12 110.13 Address
(6) 564 Billy the Kid 65.45 0 Address
(7) 564 Billy the Kid 45.60 0 Address
(8) 564 Billy the Kid 65.20 176.25 Address

SUM
 

Hi,

Try this

=if(B1=B2,0,sumif($B$1:$B$8,B1,$C$1:$C$8))

This will work as long as all same numbers appear together - which happens
to be the case in your data presented below

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Talladega" wrote in message
...
Hi,

I have a few hundred rows. Each row contains a person information and
amount. There are multiple rows with the same person, but different
amount.
I want to create a formula in each row on a separate column (D) that only
adds the amount for the same person. I do not want to use subtotal
because
it adds an additional row without retaining the person info. In column D
the
formula only shows a zero on each row if there are multiple amounts with
the
same person. The last row should be the sum of all the amounts from the
same
person. I hope this makes sense.

(A) (B) (C) (D) (E)
(1) 123 John Doe 24.16 0 Address
(2) 123 John Doe 16.45 40.61 Address
(3) 452 Sarah Jane 54.56 0 Address
(4) 452 Sarah Jane 45.45 0 Address
(5) 452 Sarah Jane 10.12 110.13 Address
(6) 564 Billy the Kid 65.45 0 Address
(7) 564 Billy the Kid 45.60 0 Address
(8) 564 Billy the Kid 65.20 176.25 Address