Discussion Re: What is this Formula doing in Excel
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982565#M122894
Hi <LI-USER uid="214174" login="Peter Bartholomew"></LI-USER><BR /><BR />Thanks for the reply. That makes sense and is really helpful.<BR /><BR />The part that I would like to understand better is "*"&Mylist&"*". What is happening in the formula here?Thu, 18 Nov 2021 21:48:32 GMTRP1232021-11-18T21:48:32ZWhat is this Formula doing
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982378#M122883
Hi all.<BR /><BR />Can anyone help with this formula. =SUM(COUNTIF(A2,"*"&Mylist&"*"))<BR /><BR />I use it to match data from a table to that in a spreadsheet.<BR /><BR />What I would like to know is how the formula doing this? I know what it is supposed to do and the results are correct but how is it matching the same numbers?<BR /><BR />Any help would be appreciated.Thu, 18 Nov 2021 20:40:59 GMThttps://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982378#M122883RP1232021-11-18T20:40:59ZRe: What is this Formula doing
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982473#M122886
<P><LI-USER uid="1223016"></LI-USER> </P><P>The formula tests A2 against each member of MyList in turn to see whether it is contained within the string A1. The resulting array of counts is summed so that multiple matches within the list are recognised.</P>Thu, 18 Nov 2021 21:07:50 GMThttps://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982473#M122886Peter Bartholomew2021-11-18T21:07:50ZRe: What is this Formula doing
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982565#M122894
Hi <LI-USER uid="214174" login="Peter Bartholomew"></LI-USER><BR /><BR />Thanks for the reply. That makes sense and is really helpful.<BR /><BR />The part that I would like to understand better is "*"&Mylist&"*". What is happening in the formula here?Thu, 18 Nov 2021 21:48:32 GMThttps://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982565#M122894RP1232021-11-18T21:48:32ZRe: What is this Formula doing
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982637#M122895
<P><LI-USER uid="1223016"></LI-USER> </P><P><SPAN> The formula</SPAN></P><P> </P><LI-CODE lang="excel-formula">= "*"&Mylist&"*"</LI-CODE><P> </P><P>is an array of text strings, each one preceded and followed by a "<STRONG><FONT color="#0000FF">*</FONT></STRONG>". The "<FONT color="#0000FF"><STRONG>*</STRONG></FONT>" is a <U>wildcard</U> that matches any substring so making "<FONT color="#0000FF">"</FONT><STRONG>*</STRONG><SPAN>&</SPAN><STRONG><FONT color="#FF00FF">in</FONT></STRONG><SPAN>"&"</SPAN><FONT color="#0000FF"><STRONG>*</STRONG></FONT><SPAN>" a match for "</SPAN><STRONG><FONT color="#0000FF">str</FONT><FONT color="#FF00FF">in</FONT><FONT color="#0000FF">g</FONT></STRONG><SPAN>". </SPAN></P>Thu, 18 Nov 2021 22:04:03 GMThttps://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2982637#M122895Peter Bartholomew2021-11-18T22:04:03ZRe: What is this Formula doing
https://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2991089#M123187
Hi.<BR /><BR />Thanks for the explanation. So if I've understood correctly, the whole formula is saying-<BR /><BR />Total all the times that a number from Mylist is counted in the range starting from A2, including wildcard selections?<BR /><BR />Would that be a way of explaining it? Or have a missed something?<BR /><BR />Once again, your help is greatly appreciated.<BR /><BR />RPSun, 21 Nov 2021 23:52:44 GMThttps://techcommunity.microsoft.com/t5/excel/what-is-this-formula-doing/m-p/2991089#M123187RP1232021-11-21T23:52:44Z