Countif, mode function formulas doesnt work the way I wish it should be

[moniquem]

The result for all must be W. I wish to return the most recurring direction, returning -99 if all the cells have missing data which is shown as ---.

What is the problem with the formula used?

[Jacc]

Use COUNTA, not COUNT.

[daddylonglegs]

Try this formula in A24

=IF(COUNTIF(A1:A23,"---")=COUNTA(A1:A23),"-99",INDEX(A1:A23,MODE(IF(A1:A23<>"---",MATCH(A1:A23,A1:A23,0)*{1,1}))))

confirmed with CTRL+SHIFT+ENTER and copied across

[moniquem]

Oh, you're both right! Thank you very much!

[moniquem]

@daddylonglegs. can you kindly explain to me in words the formula you've created? Starting from the INDEX function please.
And what is the difference if I just press Enter instead of Ctrl+Shft+Enter?

Thanks!

[Jacc]

Ctrl+Shft+Enter makes it into an array formula. That way functions that normally operate with single cell inputs (such as IF) can operate with a range as an input and produce a range as an output.

[moniquem]

what should be the updated codes if rows are inserted this way?

[Jacc]

First option would be to use Autofilter to get rid of the empty rows. If that is not an option then the second sheet has a formula that works.

I also wish daddylonglegs would explain his formula. I didn't get the *{1,1} part.

[moniquem]

Thanks, Jacc! i hope daddylonglegs will explain the (1,1) part.
Thank you so much!

[moniquem]

Dear Jacc, can you please explain or teach me about the set functions you used in sheet2? So I can use them in my future projects. Thank you!

[Jacc]

You are referring to this part I assume?

Please Login or Register  to view this content.

This part, (A2:A10<>"---"), creates a array of TRUE or FALSE.
This part, (A2:A10<>""), also creates a array of TRUE or FALSE.
What I want to check is if both of them are TRUE. Normally that would be done by the AND function but unfortunately AND does not work with array formulas. However, the same thing can be accomplished by multiplying the two arrays. Excel will then see TRUE as 1 and FALSE as 0. The result is the same as if I had used AND except that I get an array of 1's and 0's as a result. When using logic (such as IF) Excel regards 0 as FALSE and any number larger or smaller than 0 as TRUE.

Here's something you can try. Make a copy of Sheet2, select rows 5 to 36 (entire rows) and delete them (Ctrl + "-").
Select one of the three cells with formulas. Under the Formulas ribbon click the Evaluate Formula button. The reduced dataset will allow you to follow the calculations step by step in the evaluate window.

Does this make sense?