software

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=quot;a-1quot;)*(B1:B7=quot;s-1quot;))

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 quot;aquot; in Col A and only where quot;s-1quot; 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.If any quot;aquot; means starting with quot;aquot;

=SUMPRODUCT(--(LEFT(A1:A7)=quot;aquot;),--(B1:B7=quot;s-1quot;))

if it means anywhere

=SUMPRODUCT(--ISNUMBER(SEARCH(quot;aquot;,A1:A7)),--(B1:B7=quot;s-1quot;))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

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