MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF within a LARGE


Posted by Craig Williams on July 30, 2001 7:03 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:07 AM

I'LL TAKE THIS STATEMENT AS THE TRUE STATE OF AFFAIRS IN WHAT FOLLOWS.

SAMPLE DATA

YOU MEAN OF COURSE: 12 (FROM D20:D24), 11 (FROM D12:D16), AND 4 (FROM D4:D8)

ALL RANGES TAKEN TOGETHER, THE "WORSEST SCORE" IS 12.

YOUR SAMPLE DATA SEEM TO SUGGEST THAT, IN A BLOCK OF 4 CELLS, YOU CAN HAVE AT MOST ONE OF [DNS, DNF, DNC, DSQ]. IF THIS IS *CORRECT*, THEN USE:

=MAX(MAX(D4:D7,COUNTIF(D4:D7,"DN?")*11,COUNTIF(D4:D7,"DSQ")*12),MAX(D12:D15,COUNTIF(D12:D15,"DN?")*11,COUNTIF(D12:D15,"DSQ")*12),MAX(D20:D23,COUNTIF(D20:D23,"DN?")*11,COUNTIF(D20:D23,"DSQ")*12))

NOTE 1. I OMITTED THE CELL REFS D8, D16, & D24 FOR THEY SEEM TO CONTAIN SOME KIND OF SUMMARY IRRELEVANT WRT DETERMINING THE WORSEST SCORE.

NOTE 2. YOU CAN ALSO DETERMINE THE MAX PER BLOCK SCORES IN APPROPRIATE CELLS AND APPLY MAX ON THOSE CELLS.

OR ARRAY-ENTER

=MAX(D4:D23,IF(ISNA(LOOKUP(D4:D23,{"DNC","DNF","DNS","DSQ"},{11,11,11,12})),"",LOOKUP(D4:D23,{"DNC","DNF","DNS","DSQ"},{11,11,11,12})))

NOTE 3. IN ORDER TO ARRAY-ENTER A FORMULA, YOU NEED TO HIT CONTROL+SHIFT+ENTER AT THE SAME TIME (NOT JUST ENTER) TO ENTER IT.

Aladin