Problem:
List1 (column A) contains strings composed of the letter \"T\" and a number (i.e. T1,T2,,,,T100).
We want to count the number of strings in List1 which contain a number smaller than the one in a specified string of the same format.
Solution:
Use the SUMPRODUCT, SUBSTITUTE, and VALUE functions as shown in the following formula:
=SUMPRODUCT(--(VALUE((SUBSTITUTE($A$2:$A$7,\"T\",\"\")))
(SUBSTITUTE(B2,\"T\",\"\"))))
Bookmarks