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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
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,309
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,309
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,309
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,950
Members
409,848
Latest member
Blomsten
Top