Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 684
user2285265 Avatar asked Oct 29 '13 21:10

user2285265


2 Answers

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

like image 109
pnuts Avatar answered Oct 07 '22 14:10

pnuts


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

like image 29
Jaycal Avatar answered Oct 07 '22 14:10

Jaycal