Count and rank/sort

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Just curious as to how to perform a count operation to determine how many cells to rank. What I have is this:
{=SMALL(G18:G23,ROW(INDIRECT("1:"&ROWS(I18:I23))))}
on the range I18:I23
If there are only values in G18 and G19, the whole "I" range displays #N/A
What I want is for it to only do the operation on the cells that have values. Help? Thanks in advance. Let me know if there is any more clarification necessary.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
Is this what you mean?

=IF(ROW()-ROW(I18)+1<=COUNT(G18:G23),SMALL(G18:G23,ROW(INDIRECT("1:"&ROWS(G18:G23)))),"")
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
Can you provide a sample of the data, along with the actual/expected results?
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188

ADVERTISEMENT

Here is the spreadsheet. So basically, I want excel to rank them and then sort based on any changes in the date in Column F.

<Edit> Deleted HTML because there were stupid errors in it. See next post.
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Forget that one above. I saw the errors, but now I want to be able to clear data in columns E and F and not have it throw #N/As all over the place. Help? Also, disregard column G. It doesn't have anything to do with anything. It was there before I figured out how to get rid of tie rankings.
Thanks again for any help.
Book1.xls
DEFGHIJK
17NameDate
181Patrick10/31/200611Patrick10/31/2006
1910Doug10/15/2006102James10/26/2006
202James10/26/200623Johnny10/22/2006
2113Mike10/13/2006134Buddha10/21/2006
223Johnny10/22/200635Confuscious10/20/2006
2315Eric10/12/2006156Descartes10/19/2006
2414Phoebe10/13/2006137Socrates10/18/2006
2512Carie10/14/2006128Plato10/17/2006
2611Caroline10/15/2006109Jesus10/16/2006
279Jesus10/16/2006910Doug10/15/2006
288Plato10/17/2006811Caroline10/15/2006
297Socrates10/18/2006712Carie10/14/2006
306Descartes10/19/2006613Mike10/13/2006
315Confuscious10/20/2006514Phoebe10/13/2006
324Buddha10/21/2006415Eric10/12/2006
Sheet1
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188

ADVERTISEMENT

Bump for any additional help besides Domenic.
Thanks.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
Try...

D18, copied down:

=IF(N(F18),RANK(F18,$F$18:$F$32)+COUNTIF($F$18:F18,F18)-1,"")

J18, copied down:

=IF(N(I18),VLOOKUP(I18,$D$18:$F$32,2,FALSE),"")

K18, copied down:

=IF(N(I18),VLOOKUP(I18,$D$18:$F$32,3,FALSE),"")

Hope this helps!
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
ummm...That works beautifully.
So it just checks to see if it's a number?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
If it's a number (a date is stored as a serial number), it returns that number and the IF statement is evaluated as TRUE. If it's blank, it returns 0 and the IF statement is evaluated as FALSE. See the help menu for additional information...

Hope this helps!
 

Forum statistics

Threads
1,141,140
Messages
5,704,522
Members
421,353
Latest member
jekoxien15

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