Forum Discussion
What is this Formula doing
Hi all. Can anyone help with this formula. =SUM(COUNTIF(A2,"*"&Mylist&"*")) I use it to match data from a table to that in a spreadsheet. What I would like to know is how the formula doing th...
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.
Hi PeterBartholomew1
Thanks for the reply. That makes sense and is really helpful.
The part that I would like to understand better is "*"&Mylist&"*". What is happening in the formula here?
Thanks for the reply. That makes sense and is really helpful.
The part that I would like to understand better is "*"&Mylist&"*". What is happening in the formula here?
The formula
= "*"&Mylist&"*"is an array of text strings, each one preceded and followed by a "*". The "*" is a wildcard that matches any substring so making ""*&in"&"*" a match for "string".
- Hi.
Thanks for the explanation. So if I've understood correctly, the whole formula is saying-
Total all the times that a number from Mylist is counted in the range starting from A2, including wildcard selections?
Would that be a way of explaining it? Or have a missed something?
Once again, your help is greatly appreciated.
RP