Calculate Company Revenue without Duplicating

[purplesamcat]

I have a spreadsheet with:

Tier	Company Name	Company Revenue	Product Line	Product $
Top	ABC	$15,000,000	Product 1	$45
SMB	DEF	$7,000,000	Product 2	$50
Regional	GHI	$27,000,000	Product 1	$100
Top	ABC	$15,000,000	Product 2	$100
Top	ABC	$15,000,000	Product 1	$75
Top	JKL	$25,000,000	Product 1	$25
etc.

<tbody>
</tbody>

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

Tier	Company Revenue	Total Product $
Top	$40,000,000	$245
Regional	$27,000,000	$100
etc

<tbody>
</tbody>

 

[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.