Countif Cells with * not counting as below a value

Question

I'm having some issues counting values in excel which contain *

I am comparing a document version number to the version a user has read, with the * to signify full training completed on the SOP.

I have a countif formula counting every cell which is below the version number to give me the number of people who are out of date on each SOP.

My countif formula is as follows.

=COUNTIF(C2:K2,"<"&A2)+COUNTIF(C2:K2,"<"&(A2&"~*"))

The formula is in the cell under OOD with the version number being A2 and the cells below staff 1-9 being C2:K2.

As you can see it is counting 4 entries as OOD but Staff 2-6 are OOD so the formula should return 5. It is counting 1* correctly as OOD and 9 as OOD but 2* is not recognised as below 10. If you need any more information please let me know!

Answer 1

2* is not less than 10* in Excel. When comparing strings it compares character to character so 2 is greater than 1 and it comes back as false.

Switch to an array version of SUM and SUBSTITUTE:

=SUM(--(IFERROR(--SUBSTITUTE(C2:K2,"*",""),"X")<A2))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.