Prev: Interval between now and next birthday
Next: copy formula
From: Talladega on 15 Apr 2010 20:11
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

From: FSt1 on 15 Apr 2010 20:36
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
>
From: Ashish Mathur on 15 Apr 2010 21:47
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" <Talladega(a)discussions.microsoft.com> wrote in message
news:0E22F826-0E7D-4DBC-A25D-BCB6B8EA8472(a)microsoft.com...
> 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
>
 | 
Pages: 1
Prev: Interval between now and next birthday
Next: copy formula