Issues with =SUMPRODUCT((A$3:A$160=C2)*(B$3:B$160="M"))

[Cmorgan]

Evening,

I am using the formula:

=SUMPRODUCT((A$3:A$160=C2)*(B$3:B$160="A"))

However, I now need to change the "A" at the end to A*, however this causes it to confuse the A* and A.

Any ideas how to adapt the formula would be appreciated.

Thanks

[FDibbins]

However, I now need to change the "A" at the end to A*, however this causes it to confuse the A* and A.

if you mean A followed by a period, thats exactly what that will do...it will look for anything starting with A - apple, abc, a123, all of us....you name it

[Pete_UK]

If A* is the only 2-character entry, then you could make the second term:

(LEN(B$3:B$160)=2)

or you could add that on as a third term with an * before it (i.e. AND).

Hope this helps.

Pete

[Ron Coderre]

This regular formula returns the count of rows where the Col_A value matches C2 and the Col_B value contains 2 or more characters and begins with A

Please Login or Register  to view this content.

Is that something you can work with?

[Cmorgan]

Many thanks

[Ace_XL]

How about..

=SUMPRODUCT((A$3:A$16=C2)*(EXACT(B$3:B$16,"A*")))