Calculate Company Revenue without Duplicating

[purplesamcat]

I have a spreadsheet with:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Tier[/TD]
[TD]Company Name[/TD]
[TD]Company Revenue[/TD]
[TD]Product Line[/TD]
[TD]Product $[/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]ABC[/TD]
[TD]$15,000,000[/TD]
[TD]Product 1[/TD]
[TD]$45[/TD]
[/TR]
[TR]
[TD]SMB[/TD]
[TD]DEF[/TD]
[TD]$7,000,000[/TD]
[TD]Product 2[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]Regional[/TD]
[TD]GHI[/TD]
[TD]$27,000,000[/TD]
[TD]Product 1[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]ABC[/TD]
[TD]$15,000,000[/TD]
[TD]Product 2[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]ABC[/TD]
[TD]$15,000,000[/TD]
[TD]Product 1[/TD]
[TD]$75[/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]JKL[/TD]
[TD]$25,000,000[/TD]
[TD]Product 1[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want a report that shows Total Company Revenue by Tier without duplicate counting (I have 6,000 lines)

When I run a pivot report.... it sums the Company Revenue..... I could do Averages but then the total is the average too.... Like to End up with a report that shows

[TABLE="width: 500"]
<tbody>[TR]
[TD]Tier[/TD]
[TD]Company Revenue[/TD]
[TD]Total Product $[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]$40,000,000[/TD]
[TD]$245[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Regional[/TD]
[TD]$27,000,000[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Your request is a bit unclear, but maybe SUMIF ?

 

[sheetspread]

You can add a helper column that divides revenue by the count of unique items within a group, then the pivot table will give you the desired sum:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Tier	Company Name	Company Revenue	Product Line	Product $	adj comp rev
2	Top	ABC	$15,000,000	Product 1	$45	$5,000,000			Row Labels	Sum of adj comp rev	Sum of Product $
3	SMB	DEF	$7,000,000	Product 2	$50	$7,000,000			Regional	27,000,000	100
4	Regional	GHI	$27,000,000	Product 1	$100	$27,000,000			SMB	7,000,000	50
5	Top	ABC	$15,000,000	Product 2	$100	$5,000,000			Top	40,000,000	245
6	Top	ABC	$15,000,000	Product 1	$75	$5,000,000
7	Top	JKL	$25,000,000	Product 1	$25	$25,000,000
Sheet11

Cell Formulas
Range	Formula
F2	=C2/COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2)

 

[purplesamcat]

But with over 6,000 lines it is really difficult to do this? Any other shortcut recommendations or formulas?

 

[sheetspread]

How slow is it running? You can paste as values too.