Count Top % Contributors

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Oct 26, 2006
Messages
358
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
Joined
Jun 8, 2007
Messages
2,494
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
Joined
Oct 26, 2006
Messages
358

ADVERTISEMENT

Thanks Schwarzmanne

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

JVN
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Oct 26, 2006
Messages
358
Thanks LASW10

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

JVN
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,108
Messages
5,599,760
Members
414,336
Latest member
Nicolas2465

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top