Wildcards in Array Formulas

[Babymech]

I've been struggling all day to go get a good COUNTIF with multiple criteria
going, and I think I might be closing in on a solution. What I've been doing
now is using

{=SUM((NamedRange1="Text1")*(NamedRange2="Text2"))}

This way I've been able to get a count of how many rows there are where I
find both cells containing "Text1" and cells containing "Text2". There are
four cells containing "Text1" and two cells containing "Text2" but only one
place where they intersect, meaning that the formular returns "2". So far so
good. However, it is absolutely necessary that I be able to use wildcards in
this, as I can only identify the first two characters of "Text2". That means
that I want to, ideally, use:


{=SUM((NamedRange1="Text1")*(NamedRange2="Te*"))}

This returns 0 when it should return 1. I've tested this by using:

{=SUM((NamedRange2="Text2")*1)

versus

{=SUM((NamedRange2="Te*")*1)

The first one returns "1" whereas the second returns "0." This is incredibly
frustrating - if someone has the answer to this I'd be very grateful. If you
don't, maybe you can show me a better way of accomplishing what I want to do
here? Thanks a lot.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(LEFT(NamedRange1,5)="Text1"),
--(LEFT(NamedRange2,2)="Te"))


This is a bit more efficient than using the {=SUM(a * b)} array formula.

See http://www.mcgimpsey.com/excel/doubleneg.html for mroe.