In all cases the "numbers are stored as text" - the fact that General format has been applied to I:K has no effect on the underlying values.
The issues you have re: calculation relate solely to blanks within the precedent ranges (previously unknown condition).
You get #VALUE! errors in the 2nd examples because of the lack of TEXT(range,"000") which forces the blanks to become Null (in effect) - without it the MOD of the MIDs will fail on the blanks (#VALUE!)
For the same reason you get "over-count" in the version WITH the TEXT as the blanks are seen as 000 and per your requirements 0 is included as valid - hence all the blanks are added to the final count.
Given all of the above, you can use:
Note however that your manually calculated value for column B is incorrect and the result should be 6 rather than 4 (B5,B8,B13,B17,B19:B20)
Bookmarks