finding top values and counts

[jturn00]

Hi


I have a list of data that I am looking to find the sum for the top 80% of the data and the count of number of unique rows in that data. (i.e. large, medium, small)


I only want to do this for Sales person x. I know I can do some of this in a pivot table but I am looking to do it using sumproduct or aggregate since this will be shared and was told not to use array formulas or pivot tables.


Thoughts?


Also, I ran this formula [SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15),--(A2:A15=x))] on the sample data below and for the party field, (ABC, XYZ etc) for only sales person x, I got 2.916. (I think the answer is 4 for the number of unique party values for sales person x.


Having sample data like:

Sales Person Party Purchase Qty

x ABC 20

x XYZ 30

x HML 40

x ABC 50

x ABC 40

x HML 50

x NKY 30

x ABC 20

x XYZ 30

x HML 40

y ABC 50

y ABC 40

y HML 50

y NKY 30

 

[Haseeb A]

Hello Jturn, Welcome to Chandoo. Your first step to becoming awesome in excel.


For unique count;


=SUMPRODUCT((A2:A150="x")*(B2:B150<>"")*(MATCH(A2:A150&"|"&B2:B150,A2:A150&"|"&B2:B150,0)=ROW(A2:A150)-ROW(A2)+1))


Adjust the range. Do not use whole column reference like, A:A, use a limited range or dynamic range.


I am not sure about 'Sum of the top 80%'. Can you please explain?