How to SUM parts of a column which have same text value in different column in the same row

Question

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.

Solution 1

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

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

Solution 2

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

Solution 3

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

Comments

[Jaycal]

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.