1

I have values I need to search for within another sheet (4 columns), the fourth column's cells containing strings of text. I need a formula that is able to search for and count how many times the value shows up in the other sheet, even within a string of text. Preferably I'd like it find the exact match of the text. Sometimes a cell will be 'abcd' and it will count 'abcd1.'

=COUNTIF(Sheet2!A:D, A30)

So far this only gets me the amount of times the value shows up when on its own in a cell, but not within the string of text. I believe there will also be some concatenating involved for some of the values. Suggestions?

player0
  • 124,011
  • 12
  • 67
  • 124
andie_6655321
  • 55
  • 6

1 Answers1

0

try:

=COUNTIF(Sheet2!A:D, "*"&A30&"*")

or:

=INDEX(SUM(IFERROR(REGEXMATCH(Sheet2!A:D&"", ".*"&A30&".*")*1, 0)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • The top one worked, thank you!! Can you explain what's going on at the end? before i was using asterisks, but not within quotations. – andie_6655321 Nov 19 '21 at 23:31
  • 1
    @andie_6655321 sure, we just appended asterisk wildcard `*` in front and at the end so countif will try to find A30 even in those cells which contains A30 and something more in either direction – player0 Nov 19 '21 at 23:36