Attempting to get sum of hours assigned to a particular contact
I'm trying to get a formula working that will do the following:
Parse 3 columns of contacts and for each time that contact is listed, start adding the hours that are assigned to that contact. Each row is considered a different project.
I used this formula as a test for just one column (I'm a formula rookie). Note, the "proposed engineers" don't look like an actual contact, but on my actual sheet, we use actual contacts, if that makes sense or can impact a formula.
=SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], [Proposed Engineer 1] = [Proposed Engineer 1]@row, [Engineer 1 Hours]:[Engineer 1 Hours])
I was trying to get it working for just one column and I keep getting #UNPARSEABLE. It does highlight the columns though.
I'd like this calculation to happen, so that I can have it pushed to a report (so I can create a dashboard for it)
Iron Man  150
Hawkeye  100
Thor  175
Hulk  175
Black Widow  175
Anyone have any idea of how to get this working? I thought about using an INDEX function as well but everything I saw for doing math on an Index suggested SUMIF instead
TIA
Best Answer

Thats amazing. Thanks @Devin Lee for putting that together!
Answers

In simple terms you can use the following
=SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Iron Man", [Engineer 1 Hours]:[Engineer 1 Hours])
Then we can chain them together to get the data from all three columns
=SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Iron Man", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Iron Man", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Iron Man", [Engineer 3 Hours]:[Engineer 3 Hours])
After that we go into Hulk Mode and smash that last formula together multiple times, changing the names each time, and adding a line break between them.
="Iron Man  " + (SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Iron Man", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Iron Man", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Iron Man", [Engineer 3 Hours]:[Engineer 3 Hours])) + CHAR(10) + "Hawkeye  " + (SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Hawkeye", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Hawkeye", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Hawkeye", [Engineer 3 Hours]:[Engineer 3 Hours])) + CHAR(10) + "Thor " + (SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Thor", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Thor", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Thor", [Engineer 3 Hours]:[Engineer 3 Hours])) + CHAR(10) + "Hulk " + (SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Hulk", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Hulk", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Hulk", [Engineer 3 Hours]:[Engineer 3 Hours])) + CHAR(10) + "Black Widow " + (SUMIF([Proposed Engineer 1]:[Proposed Engineer 1], "Black Widow", [Engineer 1 Hours]:[Engineer 1 Hours]) + SUMIF([Proposed Engineer 2]:[Proposed Engineer 2], "Black Widow", [Engineer 2 Hours]:[Engineer 2 Hours]) + SUMIF([Proposed Engineer 3]:[Proposed Engineer 3], "Black Widow", [Engineer 3 Hours]:[Engineer 3 Hours]))

Thats amazing. Thanks @Devin Lee for putting that together!
Help Article Resources
Categories
Check out the Formula Handbook template!