Formula that references Drop down list and associated values and give total

Matt Eade

Hope this is possible....

I have several projects with team members assigned accross these different projects.

 

I want to be able to see the total hours each person has been assigned accross all projects. 

See screen image below. "Matt D." is part of a dropdown list of names.

 

In this case Matt is assigned to 2 different projects with 5 hours ea. I'm looking for a way under the roll up section to "find" and Matt D and the hours assigned and to show the total. 

 

I can do this manualy but need an automated way to acomplish this. 

 

Any idea if this is possible?

 

Thanks!

 

Stephen Uekert

Hi there Matt!

 

I'm thinking the SUMIF function may work for you. The formula reads as follows:

 

SUMIF(criteria_range, criteria_value, sum_range)

 

I don't know the names of your columns so I'll just make some up for the example. I'll call the column that has the drop down list of names, Names, and the column that has the numbers in it, Hours. I also don't know your cell range so you'll need to know that too. So in the cell under Roll Up next to Matt, you would input:

 

=SUMIF(Names1:Names20, "Matt D.", Hours1:Hours20)

 

Just for the example I used a cell range of 1 through 20. Yours may be bigger or smaller.

 

In any case, I think that formula is right (Not sure I would bet on it though LOL).

 

Hope it works for you! Take care and have a good day,

 

Steve