Counting Text Occurances using "*" Wildcard

  • Thread starter Thread starter Keith Riley
  • Start date Start date
K

Keith Riley

I am trying to count the number of rows in which two
conditions are true and the function does not appear to be
working as the Help describes.
My function is:
=SUM(IF($G$2:$G$32=CONCATENATE("*",$A90,"*"),IF
($H$2:$H$32=B$86,1,0)))
that is count the number of rows in which column G
contains, within the text, the calue in A90 AND the
contents of column H contains exactly the contents of B86.

The problem seems to be in the use of the "*" wildcard -
when I eliminate that condition or hardcode a specific
operand the functions works.

To test this I coded function
=IF("xtest"="*test",TRUE,FALSE) and it returns FALSE
seeming to indicate that the "*" isn't acting as a
substitute for zero or more characters. How should the "*"
wildcard work in a comparison function?
 
Try = sumproduct((g2:g32=a90)*(h2:h32=a90)) if I understand you correctly

Bruce Girvitz
 
One way:

=SUMPRODUCT(--NOT(ISERR(SEARCH("*" & $A90 & "*",
$G$2:$G$32))),--($H$2:$H$32=B$86))
 
Keith,

try something like this instead

=SUMPRODUCT((ISNUMBER(FIND($A90,$G$2:$G$32)))*
($H$2:$H$32=B$86))

wildcards only works in some instances and find
will be the equivalent. Note that find is case sensitive
so if you want to be able to use and case replace it with
search

regards,

Peo Sjoblom
 
I am trying to count the number of rows in which two
conditions are true and the function does not appear to be
working as the Help describes.
My function is:
=SUM(IF($G$2:$G$32=CONCATENATE("*",$A90,"*"),IF
($H$2:$H$32=B$86,1,0)))
that is count the number of rows in which column G
contains, within the text, the calue in A90 AND the
contents of column H contains exactly the contents of B86.

The problem seems to be in the use of the "*" wildcard -
when I eliminate that condition or hardcode a specific
operand the functions works.
Click to expand...

There's nu support for wildcards outside certain (few) functions. In your case,
you're trying to use wildcards in an equality comparison. Won't work.

Here's another alternative.

=SUMPRODUCT(--(SUBSTITUTE($G$2:$G$32,$A90,"")<>$G$2:$G$32),--($H$2:$H$32=B$86))

which I believe minimizes the number of function calls, though it may be slower
than Peo's formula.
 
Back
Top