SUMIF Formula works but giving too many results

**kaplanj23** · 09-26-2014, 10:22 AM

I am currently Using the SUMIF formula to add numbers in Column B that match names in Column A. I am using =SUMIF(A:A,A2,B:B) and dragging the box down after the formula spits out the first response. This works great except if I have 5 names that match in Column A the formula will give me the same number answer 5 times in column C. Is there a way to prompt the formula to only give me 1 answer in column C per matching name? Am I making sense? Thanks so much guys and gals.

**boopathiraja** · 09-26-2014, 10:27 AM

TRy this one will return the 1st match

Formula:

Please Login or Register  to view this content.

**Olly** · 09-26-2014, 10:27 AM

In C2, enter:

Formula:

Please Login or Register  to view this content.

Copy down.

**Speshul** · 09-26-2014, 10:30 AM

Another alternative,

=IFERROR(IF(COUNTIF(A$1:$A1,A1)=1,1,"")*SUMIF(A:A,A1,B:B),"")

**martindwilson** · 09-26-2014, 10:38 AM

here are 3 ways

**Speshul** · 09-26-2014, 10:42 AM

That sample file has a pretty misleading title.

**kaplanj23** · 09-26-2014, 10:53 AM

Thanks for the quick responses everyone. I ended up using =IF(COUNTIF($A$1:$A2,$A2)=1,SUMIF($A:$A,$A2,$B:$B),"") and it works great on the template i created. When I try to change the letters to X and Y (This is where the data is on my actual business spreadsheet) and the numbers to 6 and 7 It puts the final number one cell too high. For instance if cells x10,x11,x12 all match the formula spits out the sum on cell x9. Obviously I made a mistake in transferring the formula should I change the formula to? It is currently at =(COUNTIF($Y$6:$Y7,$Y7)=1,SUMIF($Y:$Y,$Y7,$X:$X),"")