0

Effectively, I want a vlookup but for multiple things then add it at the end.

For example:

      1          2          3                 4
a   Title 1   Name1     Description 1         6
b   Title 2   Name2     Description 2         2
c   Title 2   Name2     Description 2         14

Basically, I want to find all of "Name 2" in column 2 then add the number in column 4. I want to do this to each unique name.

How do I do that?

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
rot1npieces
  • 1
  • 2

1 Answers1

1

MacroMarc and Scott Craner both pointed you in good directions ...

Method 1 - Formula

Also see this post

Assuming you put a header on your table at Row 1, and your data is in Columns A through D, put this CSE formula in F2 and fill down. (the $20 will change, de

=IF(INDEX($B$2:$B$20, MATCH(0, COUNTIF($B$1:B1, $B$2:$B$20), 0))=0,"",INDEX($B$2:$B$20, MATCH(0, COUNTIF($B$1:B1, $B$2:$B$20), 0)))

Then put this formula in G2 and fill down

=IF(SUMIF($B$2:$B$20,"="&F2,$D$2:$D$20)=0,"",SUMIF($B$2:$B$20,"="&F2,$D$2:$D$20))

You will get a result that looks like this

enter image description here

Method 2 - Pivot Table

Select anywhere inside your table and pick Insert -> Pivot Table. Put it on a new worksheet.

Put the "Name" field in the Rows and the "Count" field in the Values - it will be a SUM by default. You get the following result ...

enter image description here

Either method takes less than two minutes. 8)

Community
  • 1
  • 1
OldUgly
  • 2,129
  • 3
  • 13
  • 21