Sum Totals Based on Last name

Question

Hello, 

I am looking for an Excel Formula that will allow me to sum totals based on two criteria - Last name and first name. 

I have a list of clients, and would like to add the totals associated with the money they spent. 

The list is 1350 rows big, and would prefer not to change the formula according to all of the last names 

This is an example of what my SpreadSheet looks like: 

If anyone could give me a specific formula for this type of scenario, that would be great. I am fairly new to Excel, and I use a Mac - Excel Version 14.2.0

Thank you! 

Michelle

Answer 1

Hi,

Last Name	First Name	Prices	Total
Smith	John	100	300	=SUMIFS($C$2:$C$10000,$A$2:$A$10000,A2,$B$2:$B$10000,B2)
Smith	John	200	300
Smith	Jane	123	573
Smith	Jane	450	573
Andrews	Carol	1500	1800
Andrews	Carol	300	1800
Bob	Billy	200	500
Bob	Billy	300	500

The formula in D2 is in the right cell. Drag D2 down to the last name.

A better alternative is the use of a pivot table.

Answer 2

Hi, insert a row on top of your data and in C1 enter 

=Subtotal(9,C2:C100)

then filter column A by last name and the sum will change accordingly

Answer 3

I was hoping to get a total beside all of the names - for each last name in the D (total) column

Answer 4

Hi,

in D2:

=SUMIF($A$2:$A$100,A2,$C$2:$C$100)

Answer 5

That only gives me the total for the first name on the list. Is there a way to apply the formula to all last names and first names?