How to Count Substrings InTwo Columns
I know that using the following SUMPRODUCT function below on the data also
shown below, I get a count result of 2 for matches found in rows 1 and 3.
=SUMPRODUCT((A1:A7="a-1")*(B1:B7="s-1"))
Col A Col B
Row 1 a-1 s-1
Row 2 a-1 s-2
Row 3 a-1 s-1
Row 4 a-4 s-3
Row 5 a-5 s-1
Row 6 b-1 t-1
Row 7 b-2 t-2
However, I can't seem to figure out how to use substrings such that I want
to match any "a" in Col A and only where "s-1" appears in Col B. Using the
data above, I would expect a count result of 3 for counting rows 1, 3 and 5.
Any suggestions would be greatly appreciated. And, thanks in advance.
|