Count and Rank

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,102
I have a large data set where I am trying to count our top 10 codes. The top 10 codes today may be different come the next month. So as the quantity changes, I need to count the latest 10 highest volumes.
I have tried using countif and rank, but I am not getting the result I need.

EDIT:
I'll add a little more context.
one of the codes is 6402. This code shows up 81x's (countif). when I go to rank my top 10, I get the 81 listed for all 10 top rankings. How do I change the formula to get the 2nd, 3rd, etc. place?
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Something like this?

listcodescount of codestop codes
at
2​
a
aa
3​
t
am
1​
m
t
t
m

count of codes (D2) =COUNTIF(A:A,C2)

top codes (E2) =INDEX($C$2:$C$4,MATCH(LARGE($D$2:$D$4,ROW(A2)-1),$D$2:$D$4,0),1)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
Assuming your codes are all numeric:

Book1
ABCD
1ListTop 10Count
26524642913
36458658613
46460655812
56500644611
66581646710
7646764749
8658265729
9642065209
10643264739
11642964659
126409
136540
146474
156537
166489
176439
186595
196572
206485
216543
Sheet3
Cell Formulas
RangeFormula
C2:C11C2=MODE(IF(COUNTIF(C$1:C1,A$2:A$1001)=0,A$2:A$1001*{1,1}))
D2:D11D2=COUNTIF(A:A,C2)
Press CTRL+SHIFT+ENTER to enter array formulas.


If they're not numeric, we can adjust the formula.
 

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,102
Something like this?

listcodescount of codestop codes
at
2​
a
aa
3​
t
am
1​
m
t
t
m

count of codes (D2) =COUNTIF(A:A,C2)

top codes (E2) =INDEX($C$2:$C$4,MATCH(LARGE($D$2:$D$4,ROW(A2)-1),$D$2:$D$4,0),1)
The challenge I have with this, I have probably a 1000+ codes. I really don't want to list them out.
Thanks
 

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,102
Assuming your codes are all numeric:

Book1
ABCD
1ListTop 10Count
26524642913
36458658613
46460655812
56500644611
66581646710
7646764749
8658265729
9642065209
10643264739
11642964659
126409
136540
146474
156537
166489
176439
186595
196572
206485
216543
Sheet3
Cell Formulas
RangeFormula
C2:C11C2=MODE(IF(COUNTIF(C$1:C1,A$2:A$1001)=0,A$2:A$1001*{1,1}))
D2:D11D2=COUNTIF(A:A,C2)
Press CTRL+SHIFT+ENTER to enter array formulas.


If they're not numeric, we can adjust the formula.
I am having trouble with the first formula. I re-wrote it based on my cell structure and I am getting a #Value! response
in cell R2, I have entered =MODE(IF(COUNTIF(R$1:R1,L:L)=0,L:L*{1,1}))
Also, I am hitting ctrl+shift+enter
Thanks
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
You're probably getting the #VALUE because you have non-numeric values in the column, perhaps a header in row 1? That's one reason you should use row references instead of a whole column reference:

Excel Formula:
=MODE(IF(COUNTIF(R$1:R2,L$2:L$5000)=0,L$2:L$5000*{1,1}))

Another reason is that if you have empty cells in the column, you'll get 0 as a possible code. A third reason is performance, whole column array formulas are slow. If you want to make the range longer, you could do something like:

Excel Formula:
=MODE(IF((COUNTIF(R$1:R1,L$2:L$5000)=0)*(L$2:L$5000>0),L$2:L$5000*{1,1}))

with CSE, and change the 5000 to an appropriately high row, even 1048576, although I wouldn't recommend it. Pick the row you think will be highest you'll ever see and add 10%.
 

navic

Active Member
Joined
Jun 14, 2015
Messages
346
Office Version
  1. 2013
Platform
  1. Windows
Another way,
If you can use helper columns.

ARRAY formula in the 'G2' cell, finished with CSE (Group unique)
Code:
=IFERROR(INDEX($A$2:$A$39,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$39),0)),"")

Formula in the 'H2' cell (Count unique from the 'G' column)
Code:
=IF(G2<>"",COUNTIF($A$2:$A$39,G2),"")

Formula in the 'D2' cell (Count-Large from the 'H' column)
Code:
=IFERROR(IF(LARGE($H$2:$H$18,ROW(A1))=0,"",LARGE($H$2:$H$18,ROW(A1))),"")

Formula in the 'C2' cell (Top from the 'G:H' columns)
Code:
=INDEX($G$2:H$18,AGGREGATE(15,6,(ROW(H$2:H$18)-ROW(H$2)+1)/($H$2:$H$18=D2),COUNTIF($D$2:$D2,$D2)),1)

You need adjust the range in the formulas. Also, you can hide helper columns.
 

Attachments

  • Untitled-1.png
    Untitled-1.png
    6.2 KB · Views: 6

Forum statistics

Threads
1,175,533
Messages
5,897,971
Members
434,688
Latest member
vi28

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