Using wildcards in conditional expression

Posted by Jill on December 13, 2001 4:18 AM

Hi,

I want to count the number of occurrences two columns match specified criteria but cannot get this to work using wildcards.

This array expression works for an exact match:
=SUM(IF((Q2:Q7265="FREDERICK")*(S2:S7265="1"),1,0))

but =SUM(IF((Q2:Q7265="FRE*")*(S2:S7265="1"),1,0)) using '*' as a wildcard doesn't.

Does anyone know how to achieve this?
Jill

Posted by Aladin Akyurek on December 13, 2001 6:53 AM

Jill,

It's either (non-CSE)

=SUMPRODUCT((ISNUMBER(SEARCH("FRE",Q2:Q7265)))*((S2:S7265)+0=1))

or array-entered (CSE)

=SUM((ISNUMBER(SEARCH("FRE",Q2:Q7265)))*((S2:S7265)+0=1))

Aladin

==============

Posted by Juan Pablo G. on December 13, 2001 7:01 AM

Another option, for your case is:

=SUMPRODUCT((LEFT(Q2:Q7265,3)="LEFT")*(S2:S7265=1))

Juan Pablo G.

Posted by Juan Pablo G. on December 13, 2001 7:01 AM

I meant this

=SUMPRODUCT((LEFT(Q2:Q7265,3)="FRE")*(S2:S7265=1))

Juan Pablo G.

Posted by jacob on December 13, 2001 7:08 AM

Try using =IF(FIND("fre",Q2,1)>0,1,0))
Then fill this formula down to Q7265
Then just sum the field since it will have 1 if there is a match and 0 for no match.

Jacob

Posted by Jill on December 13, 2001 8:11 AM

Mission accomplished. Thank you all for your help.

Jill