Consolidate with multiple columns

0

Hi, I have a situation where I have four columns and am trying to consolidate them:

Column A: Project - consist of project name
Column B: Author - Individual's name
Column C: Hours - # of hours the author submitted.

So my situation is where I would have 7 rows:

Row 1 - ProjectName1, Aaron, 1
Row 2 - ProjectName1, Aaron, 3
Row 3 - ProjectName2, Aaron, 2
Row 4 - ProjectName1, Aaron, 3
Row 5 - ProjectName2, Aaron, 1
Row 6 - ProjectName2, Aaron, 4
Row 7 - ProjectName1, Aaron, 1

I want to consolidate these where it shows:
Row 1 - ProjectName1, Aaron, [Sum of hours for Project1]
Row 2 - ProjectName2, Aaron, [Sum of hours for Project2]

Consolidations seem to work only if 1 column is different and 1 column is the same. Is it possible to do it for my situation? 

Answer
Discuss

Answers

0
Selected Answer

This is a job for the SUMIFS() function. Here is a formula from the attached workbook (cell E5).

=SUMIFS($C$5:$C$11,$A$5:$A$11,$A5,$B$5:$B$11,$B5)

In fact, in the workbook I have named the ranges which makes for a formula that is much easier to read. This is the same formula as above but referring to the named ranges. This formula is in E6 in the attached workbook.

=SUMIFS(Hours,Projects,$A6,People,$B6)

There is a quick way to assign a name to a range. (1) Select the range. (2) Write the name in the Name Box (to the left of the formula bar, above the table). Once established, you can modify the named range using the Name Manager on the ribbon's Formulas tab. That is much easier and faster than to change the references in a million formulas all over the workbook. Yes, you can refer to named ranges from otehr worksheets, too.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login