Sum Duplicate Rows in Excel

[No_Hedge]

Hello,

I'm tracking truck deliveries. I have a dynamic data set. I want to consolidate the data and sum duplicate lines. Below is my example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CONCAT Code[/TD]
[TD]Delivery Date[/TD]
[TD]Customer[/TD]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]Line #[/TD]
[TD]Carrier[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/6/2017[/TD]
[TD]Oil Co.
[/TD]
[TD]Houston[/TD]
[TD]Dallas[/TD]
[TD]1[/TD]
[TD]Energy Transport[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/6/2017[/TD]
[TD]Fuel Ltd.[/TD]
[TD]Oklahoma City[/TD]
[TD]Kansas City[/TD]
[TD]1[/TD]
[TD]Tanker Co.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/6/2017[/TD]
[TD]Oil Co.[/TD]
[TD]Houston[/TD]
[TD]Dallas[/TD]
[TD]1[/TD]
[TD]Energy Transport[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/6/2017[/TD]
[TD]Oil Co.[/TD]
[TD]Houston[/TD]
[TD]Dallas[/TD]
[TD]1[/TD]
[TD]Fuel Delivery[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/6/2017[/TD]
[TD]Fuel Ltd.[/TD]
[TD]Oklahoma City[/TD]
[TD]Kansas City[/TD]
[TD]1[/TD]
[TD]Tanker Co.[/TD]
[/TR]
</tbody>[/TABLE]

I want to sum/count the "Line #" column for duplicate rows. I'm not too worried about deleting duplicate rows as once counted I can clear all duplicates in the data set. I do have a CONCAT code in Column A in order to make referencing easier. Any help is much appreciated!

 

[CalcSux78]

Welcome to the forum!

You can use a SUMPRODUCT function to find the sum of rows that match in columns B:G (with the exception of F/Line#). This is entered as an array, so may take a bit if you have a large data set. It would only be valid prior to deleting rows, so you'd have to hand type the number into line number manually. You could also create a macro to do all of this work for you. (depending on your comfort and skill level)

Excel 2010
	A	B	C	D	E	F	G	H
1	CONCAT Code	Delivery Date	Customer	Origin	Destination	Line #	Carrier
2		7/6/2017	Oil Co.	Houston	Dallas	1	Energy Transport	2
3		7/6/2017	Fuel Ltd.	Oklahoma City	Kansas City	1	Tanker Co.	2
4		7/6/2017	Oil Co.	Houston	Dallas	1	Energy Transport	2
5		7/6/2017	Oil Co.	Houston	Dallas	1	Fuel Delivery	1
6		7/6/2017	Fuel Ltd.	Oklahoma City	Kansas City	1	Tanker Co.	2
Sheet1

Cell Formulas
Range	Formula
H2	{=SUMPRODUCT(--(B:B=B2),--(C:C=C2),--(D:D=D2),--(E:E=E2),--(G:G=G2),F:F)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[No_Hedge]

CalcSux78,

This is definitely in the right direction. Since I have the identifying information concatenated in Column A, would the function look like the below?

{=SUMPRODUCT(--(A:A=A2),F:F)}

I am pretty comfortable with macros and VBA. Funny you mention it...I'm actually probably going to code a macro to apply the function and delete duplicates after function is applied. Sort of a workaround.