# Vlookup with sum and multiple returns

#### Indominus

##### Board Regular
Hello. I have workbook that lists badge numbers in column A. Hours in column H. I want to be able to add up all the hours by badge id. The problem is the a badge number can appear multiple times. So a regular Vlookup will not work. I have also tried a sumif formula. This adds them up however, it shows the duplicate and I am not looking for the duplicate to be there. This workbook can have about 5,000 rows so I am not sure exactly how this can be done. The final result I just want all the hours added up in one row with no duplicates. Attached is a sample file for an easy example. John should be added up to 5 hours. Mary added up to 11 hours. David 7 hours. Thank you!

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Fluff

##### MrExcel MVP, Moderator
Can you post some sample data including expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### kweaver

##### Well-known Member

(edited)

Book3
ABCDEFGHI
21234John35
39876Mary521
41234John2
59876Mary6
65555David77
79876Mary10
81111Kevin4040
Sheet1
Cell Formulas
RangeFormula
I2:I8I2=IF(1=COUNTIF(\$A\$2:A2,A2),SUMPRODUCT((A2=\$A\$2:\$A6)*(\$H\$2:\$H6)),"")

#### kweaver

##### Well-known Member
On second thought, I think I'd change I2 formula where there's a 6 (A6 and H6) to be something like \$A\$10 and \$H\$10 or \$A\$100 and \$H\$100 ?

#### Indominus

##### Board Regular

(edited)

Book3
ABCDEFGHI
21234John35
39876Mary521
41234John2
59876Mary6
65555David77
79876Mary10
81111Kevin4040
Sheet1
Cell Formulas
RangeFormula
I2:I8I2=IF(1=COUNTIF(\$A\$2:A2,A2),SUMPRODUCT((A2=\$A\$2:\$A6)*(\$H\$2:\$H6)),"")
This would work! Now is there anyway to remove each row when the respective I cell is not showing a value? That way I can have just a list going down. Names and hours worked going down with no extra rows showing. Not sure if there’s a way as these cells in the rows I want to delete are obviously being referenced by the formula. I just want it to show somewhere in the following format if possible. Thank you so much for your help by the way!
John 5
Mary 21
David 7
Kevin 40

#### kweaver

##### Well-known Member
I don't think so. I suspect, on first thought, you'd have to use VBA elsewhere on the sheet or filter this column to show only values.

#### Fluff

##### MrExcel MVP, Moderator

+Fluff 1.xlsm
ABCDEFGHIJKL
21234John3John5
39876Mary5Mary21
41234John2David7
59876Mary6Kevin40
65555David7
79876Mary10
81111Kevin40
9
10
Data
Cell Formulas
RangeFormula
K2:K8K2=IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/(ISNA(MATCH(B2:B100,K\$1:K1,0)))/(B2:B100<>""),1)),"")
L2:L8L2=IF(K2="","",SUMIFS(H:H,B:B,K2))

#### kweaver

##### Well-known Member
You can always count on FLUFF to prove almost anything is possible!

#### Indominus

##### Board Regular
+Fluff 1.xlsm
ABCDEFGHIJKL
21234John3John5
39876Mary5Mary21
41234John2David7
59876Mary6Kevin40
65555David7
79876Mary10
81111Kevin40
9
10
Data
Cell Formulas
RangeFormula
K2:K8K2=IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/(ISNA(MATCH(B2:B100,K\$1:K1,0)))/(B2:B100<>""),1)),"")
L2:L8L2=IF(K2="","",SUMIFS(H:H,B:B,K2))
So this works for awhile but then on row 397 it stops and creates large gaps of empty rows. Some cells filled with the names and hours via formula here and there. But large gaps. The first name and hours it does not get is row 479

#### Fluff

##### MrExcel MVP, Moderator
The ranges in the formula should be locked like
Excel Formula:
``=IFERROR(INDEX(\$B\$2:\$B\$100,AGGREGATE(15,6,(ROW(\$B\$2:\$B\$100)-ROW(\$B\$2)+1)/(ISNA(MATCH(\$B\$2:\$B\$100,K\$1:K1,0)))/(\$B\$2:\$B\$100<>""),1)),"")``

Replies
6
Views
112
Replies
2
Views
133
Replies
4
Views
212
Replies
1
Views
152
Replies
3
Views
65

1,141,715
Messages
5,708,027
Members
421,540
Latest member
quocbinh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back