12

I have a column with names and a column with numbers: 

FirstName    Name    Number
John         Smith     17
John         Smith     26
Peter        Smith     116
Peter        Smith     25
Franck       Black     17
Luke        Peterson   17
Luke        Peterson   37

Names with same FirstName and Name represent the same person. I need to sum the numbers associated with them. I prefer not to use VBA.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2285265
  • 123
  • 1
  • 2
  • 7

3 Answers3

13

A PivotTable might suit, though I am not quite certain of the layout of your data:

SO19669814 example

The bold numbers (one of each pair of duplicates) need not be shown as the field does not have to be subtotalled eg:

SO19669814 second example

pnuts
  • 58,317
  • 11
  • 87
  • 139
12

This can be done by using SUMPRODUCT as well. Update the ranges as you see fit

=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*$C$2:$C$7)

A2:A7 = First name range

B2:B7 = Last Name Range

C2:C7 = Numbers Range

This will find all the names with the same first and last name and sum the numbers in your numbers column

Jaycal
  • 2,087
  • 1
  • 13
  • 21
  • 1
    Very much so, thanks. While I'm here again, I'll change it to a 'normal' formula using `SUMPRODUCT` instead of an array formula using `SUM`. – Jaycal Sep 29 '15 at 12:30
6

If your data has the names grouped as shown then you can use this formula in D2 copied down to get a total against the last entry for each name

=IF((A2=A3)*(B2=B3),"",SUM(C$2:C2)-SUM(D$1:D1))

See screenshot

enter image description here

barry houdini
  • 45,615
  • 8
  • 63
  • 81