Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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? 

excel
MS Office Version: 2016
posted by: sorikorik (rep: 2) on Wed Mar 14, 2018 at 2:27 pm
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.

posted by: Variatus (rep: 4889) on Wed Mar 14, 2018 at 8:51 pm
Discuss


Answer the Question

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