How to create a column that adds cells with a cell in common

[ledowning]

How do I create a column with a formula that adds all the cells in one column based on similarity in another column?

I'm trying to create a column with a formula for "total time studied" that adds up 1 or more cells of study time in each subject, based on "student name." Some students only have 1 subject/cell and some have many.

I don't want to have to manually select the cells for each student, I want a formula that can add the overall study time based on the student name (or other identifier) column.

Example:

Name	Subject	Subject study time	Overall study time
John	Math	1:00	= formula that recognizes that John has 1 cell to add up
Bob	Math	0:10
Bob	Reading	0:05
Bob	Science	1:13	= formula that recognizes that Bob has 3 cell to add up

 

[Fluff]

Hi & welcome to MrExcel.
You can use Sumifs for that. SUMIFS Function

 

[Joe4]

Welcome to the Board!

Take a look at the SUMIF formula:

SUMIF function - Microsoft Support

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

support.microsoft.com

 

[ledowning]

Welcome to the Board!

Take a look at the SUMIF formula:

SUMIF function - Microsoft Support

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

support.microsoft.com

Is there a way to use the SUMIF formula and NOT have to type each student name out? Is there a formula that could simply recognize that when the name is identical in the "name" column, to add the "study time" column together? I'm trying to figure out a way to sum hundreds of students hours without needing to do any of it manually, if possible. Some students have studied once, while some have studied many times.

 

[Fluff]

You can use a cell refernce for the criteria, rather than typing out the name.

 

[ledowning]

You can use a cell refernce for the criteria, rather than typing out the name.

An example of how that formula could look would be greatly appreciated

 

[Fluff]

Like

Fluff.xlsm
	A	B	C	D
1	Name	Subject	Subject study time	Overall study time
2	John	Math	01:00	01:00:00
3	Bob	Math	00:10	01:28:00
4	Bob	Reading	00:05	01:28:00
5	Bob	Science	01:13	01:28:00
Sheet5

Cell Formulas
Range	Formula
D2:D5	D2	=SUMIFS(C:C,A:A,A2)