Vlookup with sum and multiple returns

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
126
Office Version
  1. 2016
Platform
  1. Windows
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!
samplefile.PNG
 

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
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
2,107
Office Version
  1. 365
  2. 2010
How about this?

(edited)

Book3
ABCDEFGHI
1BadgeEmpM NameUnitsQTYHrs DHrs IHrs T
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
Joined
May 8, 2018
Messages
2,107
Office Version
  1. 365
  2. 2010
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
Joined
Jul 11, 2020
Messages
126
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

(edited)

Book3
ABCDEFGHI
1BadgeEmpM NameUnitsQTYHrs DHrs IHrs T
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
Joined
May 8, 2018
Messages
2,107
Office Version
  1. 365
  2. 2010
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
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff 1.xlsm
ABCDEFGHIJKL
1BadgeEmpM NameUnitsQTYHrs DHrs IHrs T
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
Joined
May 8, 2018
Messages
2,107
Office Version
  1. 365
  2. 2010
You can always count on FLUFF to prove almost anything is possible!
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
126
Office Version
  1. 2016
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKL
1BadgeEmpM NameUnitsQTYHrs DHrs IHrs T
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
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top