MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF within a LARGE


Posted by Craig Williams on July 30, 2001 5:35 PM

Thanks for all the responses to my earlier question.
However, I forgot to mention one important fact.

What I'm trying to do is use LARGE to look at 3 different ranges say D4:D8,D12:D16,D20:D24 and return the largest number, be it decimal, or return 11 if its DNS, DNC, DNF, or return 12 if its DSQ.

Not only does the following not work (it produces a #VALUE! error) but if it did, it think would return a LARGE of 22 if there were 2 DN*'s within the range, when I really need it to return 11:
=LARGE((D4:D8,D12:D16,D20:D24,COUNTIF(D4:D8,D12:D16,D20:D24),"DN*")*11,1)

Perhaps this is a better example of what I am looking to accomplish:

1
2
3
4
[regatta 1 totals "10" - skip]
1
2
3
DNS
[regatta 2 totals "17" - skip]
1
2
8
DSQ
[regatta 3 totals "23" - skip]

Using COUNTIF to throw out the worst score from any single regatta works fine. For that I use
=MAX(D4:D8,IF((COUNTIF(D4:D6,"D*")),11,0))

But for the season championship I need to throw out the 3 worst scores that occured in any of the 3 regattas/ranges and thats where is always seems to puke.

In the above example I'm looking for (large,1) (large,2) (large,3) to return 11, 11, and 8.

I thought maybe there was a "magic" function that was eluding me. Perhaps this is not possible, or not worth the 512 character formula it requires. If you have other ideas, or need clarifations on the example, I appreciate your effort.


Posted by Aladin Akyurek on July 31, 2001 2:50 AM

See 25033.html