Understanding a Code

[Excel_77]

I'm working on a file that has the following code, can anybody explain what it does please?

=if(coutifs(c:\Storage\[file]\tab1'!$a$3:$a$2600,$a44,c:\Storage\[file]tab1'!$y$3:$y$2689,"*"&mid(n$27,5,100)&"*")>0,"yes","no")

 

[Yongle]

Put this formula in any cell on the same sheet:

="*"&MID(N$27,5,100)&"*" (A)

and this formula in another cell

=$A44 (B)


Your formula is checking to see if
BOTH
value A is found in range A3:A2600 (in tab1 of the other file)
AND
value B is found in range Y3:Y2689 (in tab1 of the other file)


If BOTH are found THEN your formula returns "yes" OTHERWISE returns "no"

 

[Special-K99]

That should be COUNTIFS and you've left out a couple of single quotes

It returns Yes if...

the value of A44 equals a value in "c:\Storage\[file]\tab1'!$a$3:$a$2600"
AND
N27 from the 5th character to the 104th character appears anywhere within a cell in "c:\Storage\[file]tab1'!$y$3:$y$2689"

otherwise No is returned

 

[Excel_77]

What does the second &"*" do?

 

[RoryA]

The asterisk is a wildcard, so let's say mid(n$27,5,100) evaluates as the word "test", your criterion is *test* which means that anything containing the text "test" (wihout quotes) will be counted.

 

[Yongle]

wildcards allow some more flexibility
* is a wildcard (any number of characters)
? is a single character widcard

Cell A1 contains value "CDEF"

this formula fails to find a match
=MATCH("CD",A1,0)

these formuas find a match
=MATCH("CD"&"*",A1,0)
=MATCH("CD" & "??",A1,0)
=MATCH("CD" & "?" &"F",A1,0)
=MATCH("CD"&"*"&"F",A1,0)