Finding maximum count NOT counting blanks

Hello All,
Using XP 2007

I have the following formula in AF3 that counts the maximun times a certain letter shows up in B3:AE3. In B3:AE3 the cells have a formula coming from another worksheet (=sheet1!A2). Each of the cells in B3:AE3 are filled in one day at a time, so on the 1st day a letter goes into B3 (say the letter "Z"). But the value in AF3 is showing blank because it reads the value in cells C3:AE3 as blanks. How can I change the formula in AF3 to look for the maximun times a certain letter shows up without count the blank cells. Thank you for you help.

Here is the formula:

{=INDEX(B3:AE3,1,MATCH(MAX(COUNTIF(B3:AE3),B3:AE3)),COUNTIF(B3:AE3,B3:AE3),0))}

Hi,
I got the original formula from the forum sometime ago, I'm not sure of the working parts to it.
The answer to your questions is: i'm just looking for the maximum times a letter shows up, not looking for the actual count of it. If a "Z" shows up 5 times and an "M" shows up 3 times, I would like the formula to show the "Z", because it shows up the max number of times in the range.

I believe that the formula looks in the range, finds the letter that shows up the maximum times and then gives that certain letter as the value. My formula works good except that it shows blanks when the blanks are the maximum in the range.

Hope this makes it better to understand.

Michael

Hi Teethless,

Thanks for youur help. When I put the formula in I receive #VALUE! error.

I tried putting in <>0 and <>"" after the (B3:AE3) but the formula does not work.


{=INDEX(B3:AE3,1,MATCH(MAX(COUNTIF(B3:AE3<>0),B3:AE3<>0) ),COUNTIF(B3:AE3<>0,B3:AE3<>0),0))}

Michael

The toothless formula will work if every cell in the range has a letter. If you have blanks as you say you need to adjust to

=CHAR(MODE(IF(B3:AE3<>"",CODE(UPPER(B3:AE3)))))

also confirmed with CTRL+SHIFT+ENTER

Note: that with MODE function if no letter appears more than once you'll get #N/A. If there is a tie, e.g. 2 "A"s and 2 "B"s then the result will be whichever of thise letters appears leftmost in the range

...or another way along similar lines to your original formula...

=INDEX(A3:AE3,MODE(IF(A3:AE3<>"", MATCH(A3:AE3,A3:AE3,0))))

confirmed with CTRL+SHIFT+ENTER

Thanks everyone for you help,

I stuck with my original formula and added...got rid of the "countif" and put in the "mode" function.

Michael