Attempting to get sum of hours assigned to a particular contact

fishey9999

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

fishey9999

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

Devin Lee

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]))

fishey9999

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