Like SUM parts of a column that have the same text value in different columns on 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 the same FirstName and Name represent the same person. I need to summarize the numbers associated with them. I prefer not to use VBA.

Answer 1

A pivot table may occur, although I'm not quite sure about your data structure:

Bold numbers (one of each pair of duplicates) do not have to be displayed, since the field should not be sub-heading, for example:

Answer 2

This can be done using SUMPRODUCT . Update ranges as you wish

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

A2: A7 = name range

B2: B7 = name range

C2: C7 = range of numbers

Here all names with the same name and surname will be found and the numbers in the numbers column are summed

Answer 3

If your data has names grouped as shown, you can use this formula in D2, copied down to get the total amount versus the last record for each name

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

See screenshot