SUMIF Function

[bigmeld]

Hello


I have a data table (columns A and B) which I could summariseusing a pivot table but don't want to as I want to perform some further work onthe data in the data table itself.

I can sum up the values for each person and can do that incolumn C using a simple SUMIF function but as you can see in green it returnsduplicates where the name in column A is repeated.

The question is; what formula would I need to achieve aresult as shown in column D (I typed the values in manually) such that the totalonly appears for the first time the person’s name is listed. So for example Sarah is listed 4 times but Ionly wish her total of 122 to be returned against the first entry for hername.

I was hoping to achieve this without VBA.

Appreciate the help folks!!





Thanks


[TABLE="width: 569"]
<tbody>[TR]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]A
[/TD]
[TD="width: 57, bgcolor: transparent"]B
[/TD]
[TD="width: 343, bgcolor: transparent"]C
[/TD]
[TD="width: 195, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Person
[/TD]
[TD="bgcolor: transparent"]Amount
[/TD]
[TD="bgcolor: transparent"]Total - formula is =SUMIF($A$2:$A$17,A2,$B$2:$B$17)
[/TD]
[TD="bgcolor: transparent"]How do I Achieve This Result?
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Andrew
[/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[TD="bgcolor: transparent, align: right"]149
[/TD]
[TD="bgcolor: transparent, align: right"]149
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]Bob
[/TD]
[TD="bgcolor: transparent, align: right"]60
[/TD]
[TD="bgcolor: transparent, align: right"]141
[/TD]
[TD="bgcolor: transparent, align: right"]141
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]Chuck
[/TD]
[TD="bgcolor: transparent, align: right"]98
[/TD]
[TD="bgcolor: transparent, align: right"]128
[/TD]
[TD="bgcolor: transparent, align: right"]128
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5
[/TD]
[TD="bgcolor: transparent"]Frank
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]Sarah
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: #92D050, align: right"]122
[/TD]
[TD="bgcolor: transparent, align: right"]122
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7
[/TD]
[TD="bgcolor: transparent"]Veronica
[/TD]
[TD="bgcolor: transparent, align: right"]63
[/TD]
[TD="bgcolor: transparent, align: right"]130
[/TD]
[TD="bgcolor: transparent, align: right"]130
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"]Chris
[/TD]
[TD="bgcolor: transparent, align: right"]73
[/TD]
[TD="bgcolor: transparent, align: right"]114
[/TD]
[TD="bgcolor: transparent, align: right"]114
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]Sarah
[/TD]
[TD="bgcolor: transparent, align: right"]55
[/TD]
[TD="bgcolor: #92D050, align: right"]122
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent"]Sarah
[/TD]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="bgcolor: #92D050, align: right"]122
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11
[/TD]
[TD="bgcolor: transparent"]Stuart
[/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12
[/TD]
[TD="bgcolor: transparent"]Bob
[/TD]
[TD="bgcolor: transparent, align: right"]81
[/TD]
[TD="bgcolor: transparent, align: right"]141
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13
[/TD]
[TD="bgcolor: transparent"]Chuck
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]128
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14
[/TD]
[TD="bgcolor: transparent"]Veronica
[/TD]
[TD="bgcolor: transparent, align: right"]67
[/TD]
[TD="bgcolor: transparent, align: right"]130
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15
[/TD]
[TD="bgcolor: transparent"]Chris
[/TD]
[TD="bgcolor: transparent, align: right"]41
[/TD]
[TD="bgcolor: transparent, align: right"]114
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16
[/TD]
[TD="bgcolor: transparent"]Sarah
[/TD]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="bgcolor: #92D050, align: right"]122
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]17
[/TD]
[TD="bgcolor: transparent"]Andrew
[/TD]
[TD="bgcolor: transparent, align: right"]99
[/TD]
[TD="bgcolor: transparent, align: right"]149
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

You can test for the name appearing previously using COUNTIF:

=IF(COUNTIF($A$1:$A1,A2)>0,"",SUMIF(A:A,A2,B:B))

 

[Fluff]

How about
=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$17,A2,$B$2:$B$17),"")

 

[bigmeld]

Ggreat, perfect solution. Many thanks!!

 

[Fluff]

Glad we could help & thanks for the feedback