# countif error

#### iain401

##### New Member
Can someone help me I can't think of a way to sort my formula

This is from a sailing results spreadsheet. Column J is the sailors result times and column K is their placings. I am using the formula below and this works fine if all the times are different. But if they are the same as in the example below it goes wrong, it should be as in column L

=1+COUNTIF(\$J\$5:\$J\$20,"<"&J5),blank)
J................... K...........L
3286..............1...........1
3286............. 1...........1
3395..............3...........2
3932..............4...........3
4265..............5...........4
4428..............6...........5

Thanks

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Would you mind if it gave 1,1,3,4,5,6? That's how I always see sports reporting, and there's a simple formula to do that
J5=RANK(\$J5,\$J\$5:\$J\$20,1)
Note the last argument is any nonzero number. See XL help on RANK.

EDIT: Bonus: you don't have to sort this way! If you still want the leader(s) on top, you can alternatively sort the RANKs rather than the times.

Try

=SUMPRODUCT(--(J5>J\$5:J\$20),1/COUNTIF(J\$5:J\$20,J\$5:J\$20&""))+1

When I tried to use the rank function before it seemed to give an error when there were cell errors or 0. Countif seems to avoid this.
I'll need to check what way they want the results maybe 1,1,3,4,5 is alright, it just seemed wrong to me. If 1,1,3,4,5 is ok then I don't need to change anything

thanks

Try

=SUMPRODUCT(--(J5>J\$5:J\$20),1/COUNTIF(J\$5:J\$20,J\$5:J\$20&""))+1

Hi Barry

Thanks,

That works if I limit the range to the cells that have values. But where there isn't a result because there were less people sailing I have #value! in some of the cells and it gives #value! in all the cells
it gives an error. Is there a way round that?

Thanks

Iain

What's the original formula that returns a #VALUE! error? Probably better to amend that so that it returns a blank and then you can amend my original suggestion to ignore blanks, i.e.

=IF(J5="","",SUMPRODUCT(--(J5>J\$5:J\$20),(J\$5:J\$20<>"")/COUNTIF(J\$5:J\$20,J\$5:J\$20&""))+1)

Note: I agree with Gates, the "normal" way of ranking would rank the lowest 2 tied values as 1 and the next as 3.....but I believe this method is also used

As to RANK, I don't see the issue with 0. You should even be able to put in a negative time for that matter(which would come in first, BTW).

Errors are no problem - I swim in them all the time. Just change
=RANK(\$J5,\$J\$5:\$J\$20,1)
to
=IF(ISERROR(RANK(\$J5,\$J\$5:\$J\$20,1)),"-",RANK(\$J5,\$J\$5:\$J\$20,1))
and RANK will correctly handle the valid numbers.

Barry's right; I've seen it both ways, but IMO the RANK way makes more sense in this case. The question to me is: if two tie for first and then there's another, isn't he really in "third" place in the race?

OTOH when two have to share the first place trophy, I guess you'd give the second place trophy to Mr. 3. Hmm...

Thanks Guys,

I think I'll make certain what way they want the results before going any further

Iain

=IF(ISERROR(RANK(\$J5,\$J\$5:\$J\$20,1)),"-",RANK(\$J5,\$J\$5:\$J\$20,1))

Unfortunately this won't produce the desired results. If J5 contains an error value then it will return "-" .....but you'll also get "-" for every other row, number or not....because RANK errors with any error in the range.

As I suggested above, you'd need to amend the formulas that produce errors to produce blanks instead and then, assuming you decide to rank using the conventional method, use

=IF(J5="","",RANK(J5,\$J\$5:\$J\$20,1))

Replies
22
Views
684
Replies
10
Views
445
Replies
6
Views
388
Replies
25
Views
663
Replies
1
Views
185

1,217,256
Messages
6,135,499
Members
449,943
Latest member
thsix

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