# Array Formula with Dynamic Wild Card Criteria

1. ## 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  Register To Reply

2. ## Re: Array Forumla with Dynamic Wild Card Criteria

Can you upload example and state what you trying to do?  Register To Reply

3. ## Re: Array Forumla with Dynamic Wild Card Criteria

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&"*")  Register To Reply

4. ## Re: Array Forumla with Dynamic Wild Card Criteria

Attached is an anonymous sheet. Originally Posted by zbor Can you upload example and state what you trying to do?  Register To Reply

5. ## Re: Array Forumla with Dynamic Wild Card Criteria 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.  Register To Reply

6. ## Re: Array Forumla with Dynamic Wild Card Criteria

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&"*")  Register To Reply

7. ## Re: Array Forumla with Dynamic Wild Card Criteria 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  Register To Reply

8. ## Re: Array Formula with Dynamic Wild Card Criteria

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

9. ## Re: Array Formula with Dynamic Wild Card Criteria

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Hello peterandrew,  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 