# 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

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

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

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

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?

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.

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

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

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

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

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,

please start your own thread if you can't find the answer for your specific query. Try to give as much relevant detail as possible, thanks

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1