Array Formula with Dynamic Wild Card Criteria

Hi All,

I have been searching, reading, and trying to figure out how to make this formula:

=SUM((MONTH('SHEET1'!$K$2:$K$145)=$C$46)*(YEAR('SHEET1'!$K$2:$K$145)=$B$45)*('SHEET1'!$F$2:$F$145=B78))+SUM((MONTH('SHEET3'!$K$2:$K$145)=$C$46)*(YEAR('SHEET3'!$K$2:$K$145)=$B$45)*('SHEET3'!$F$2:$F$145=B78))

(which works) work the way I want which would (if it worked which it doesn't) would be:

=SUM((MONTH('SHEET1'!$K$2:$K$145)=$C$46)*(YEAR('SHEET1'!$K$2:$K$145)=$B$45)*('SHEET1'!$F$2:$F$145="*"&B78&"*"))+SUM((MONTH('SHEET3'!$K$2:$K$145)=$C$46)*(YEAR('SHEET3'!$K$2:$K$145)=$B$45)*('SHEET3'!$F$2:$F$145="*"&B78&"*"))

The difference is that the reference to:

B78

would be a wild card match versus an exact match

"*"&B78&"*"

Any recommendations to how I can make this work would be great.

I've tried concatenation inline, in an external cell, using SEARCH, using ISNUMBER(FIND(, and a few other things.

Note - B78 is a string, it is not a number.

Thanks,

Michael

Attached is an anonymous sheet.

Originally Posted by zbor

Can you upload example and state what you trying to do?

Originally Posted by squiggler47

have you tried ((len('SHEET1'!$F$2:$F$145)-len(substitute('SHEET1'!$F$2:$F$145,b78,"")))>0)

to replace

('SHEET1'!$F$2:$F$145="* "&B78&"*")

I'll give it a shot and see what happens.

Originally Posted by daddylonglegs

The standard approach would be to use ISNUMBER(SEARCH, e.g.

=SUM((MONTH('SHEET1'!$K$2:$K$145)=$C$46)*(YEAR('SHEET1'!$K$2:$K$145)=$B$45)*ISNUMBER( SEARCH(B78,'SHEET1'!$F$2:$F$145)))

SEARCH isn't case-sensitive - if you want case-sensitivity switch to FIND

In Excel 2010 you could use COUNTIFS with a wildcard....

=COUNTIFS('SHEET1'!$K$2:$K$145,">="&DATE($B$45,$C$46,1),'SHEET1'!$K$2:$K$145,"<="&EOMONTH( DATE($B$45,$C$46,1),0),'SHEET1'!$F$2:$F$145,"*"&B78&"*")

The ISNUMBER worked perfect. Not sure why I had it wrong before, but it's working fine now.

Thank You,

Michael

Hi..Trying for the Array Forumla with Dynamic Wild Card Criteria