# Count Top % Contributors

#### JV0710

##### Active Member
Good day

Please can anyone help me with this query:

I have a list of about 2000 Suppliers.
In Column A is the Name of the Supplier and
in Column B is the % Contribution of the business I am doing with that Supplier -
Column B is sorted in decending order.

Part 1: In Cell C1 I would like a formula that counts the number of Suppliers that make up 80 % of the contributions. ( the closest to 80% give or take 5% )

Part 2: If the List is not sorted - What would the formula be, to count the TOP 80 % Contributions ( the closest to 80% give or take 5% )

So I would get, for example, 160 of the 2000 suppliers making up the top 80 % of the business.

Thanks

JVN

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### DonkeyOte

##### MrExcel MVP
If you have your list in sorted order why not use a cumulative % column in C

ie C2 = C1+B2 and so forth

then you can use a simple countif in D = COUNTIF(C:C,"<=80%")

I'm not saying you can't do it the more complicated way but simplifying things if and where possible would always be my recommended first step...

#### JV0710

##### Active Member
Thanks LASW10

Yes, I am doing that right now ( having a cummulative coulumn in Another sheet - just to calculate the %) . . . The thing is - This example is part of a template that I do not want to go and edit and hence the question.

The list of suppliers may also be sorted in clusters ( Suppliers by Region ) in Future and that is why I am looking for a way to count them without sorting as well

#### Mike Blackman

##### Well-known Member
Hi,

You could probably use some VBA for this;

Code:
``````Sub Count_Suppliers

MsgBox Application.CountIf(Columns("C:C"), ">=0.8")

End Sub``````

#### JV0710

##### Active Member

Thanks Schwarzmanne

I am not that familiar with VBA . . . But I will give it a try

JVN

#### DonkeyOte

##### MrExcel MVP
You could iterate in VBA using LARGE and incrementing k until such time as the sum of the LARGE array exeeeds 80 but I suspect there's a clever way of doing this without VBA... it's just I'm not sure I'm clever enough to know it... Richard Scholar is around and he may well do... and if not some of the formula whizzkids like Aladin, Barry etc will be online in the coming hours ...

The prior VBA post is working off the assumption that you have a cumulative % in C which I believe you said you didn't want ?

#### DonkeyOte

##### MrExcel MVP

Do you want to test this VBA function out ?

Code:
``````Function TopX(lmt As Long, rng As Range)
Dim i As Long
Dim val_i As Double
For i = 1 To lmt
val_i = val_i + WorksheetFunction.Large(rng, i)
If val_i > lmt Then
i = i - 1
Exit For
End If
Next i
TopX = i
End Function``````

To return count of suppliers in the 80% threshold assuming B2:B2000 contains individual % in cell C1:

=TOPX(80,B2:B2000)

#### DonkeyOte

##### MrExcel MVP
Disregard above code -- I had errors in there, sorry!

Code:
``````Function TopX(lmt As Double, rng As Range)
Dim i As Long
Dim val_i As Double
For i = 1 To rng.Rows.Count
val_i = val_i + Round(WorksheetFunction.Large(rng, i), 5)
If val_i > lmt Then
i = i - 1
Exit For
End If
Next i
TopX = i
End Function``````

Assuming values in range (B) are %

=TOPX(.8,B2:B2000)

#### JV0710

##### Active Member
Thanks LASW10

Like I said . . . I am a novice to VBA, But I will give it a bash

JVN

#### Richard Schollar

##### MrExcel MVP
if it's sorted then I can get a value using:

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 16pt; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=21 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>Ainul</TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Score</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Rich</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3">30%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bob</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3">30%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Terry</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.1">10%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ainul</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.03">3%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Howard</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.03">3%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sarah</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.02">2%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tim</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.02">2%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>9</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Karim</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.01">1%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">JinWe</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.01">1%</TD></TR></TBODY></TABLE>

=MATCH(0.8,SUMIF(OFFSET(\$B\$1,1,0,ROW(\$B\$2:\$B\$10)-ROW(\$B\$2)+1),">0"),1)

This will give the row where the % adds to 80% (or the row immediately below the one where the % exceeds 80% if there is no exact match).

The above needs to be entered with ctrl+Shift+Enter (it is an array formula).

There are probably easier ways (I would definitely use a helper column), but I can't think of how you would reproduce this if the data wasn't sorted.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,037
Messages
5,856,962
Members
431,841
Latest member
jaybeem

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

### Which adblocker are you using?

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

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