» Counting the Number of Strings which Contain Numbers Meeting Criteria

CATEGORY - Excel Counting VERSION - All Microsoft Excel Versions

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",""))))