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

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

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