text search within an array

[Samdog20]

I'm trying to search all records in column A across all records from
column B through F.

the terms in column A will be substrings within B through F.

CountIF does not return all results of the substrings, and referencing
the cell name [A2] to pull the text only pulls A2 when using
astericks.

anyone know how to Count all instances of substring text of Column A
within columns B through F?

thanks

damon

 

[David Heaton]

I'm trying to search all records in column A across all records from
column B through F.

the terms in column A will be substrings within B through F.

CountIF does not return all results of the substrings, and referencing
the cell name [A2] to pull the text only pulls A2 when using
astericks.

anyone know how to Count all instances of substring text of Column A
within columns B through F?

thanks

damon

Damon

If i understand you correctly you want to count the number of times
values in col A match those in parts of cols B thru F
if so try this

=COUNT(IF(FIND(A1,B1:F1)>1,1,0))

This is a control array so type the formula in and press Ctrl+Shift
+Enter


Regards

David

 

[Rick Rothstein]

If I understand what you want correctly (count multiple occurrences within
single cells in the range B:F for a given row, then total all of those),
then try this formula (normally entered)...

=(LEN(B1&C1&D1&E1&F1)-LEN(SUBSTITUTE(LOWER(B1&C1&D1&E1&F1),LOWER(A1),"")))/LEN(A1)

 

[Ron Rosenfeld]

I'm trying to search all records in column A across all records from
column B through F.

the terms in column A will be substrings within B through F.

CountIF does not return all results of the substrings, and referencing
the cell name [A2] to pull the text only pulls A2 when using
astericks.

anyone know how to Count all instances of substring text of Column A
within columns B through F?

thanks

damon

You didn't post your formula, or more information about the substrings missed,
but COUNTIF should work by using wildcards.

A formula like =COUNTIF(rng,"*"&A2&"*") should count all the instances of A2
within "rng" (rng could be any range of cells; e.g. B2:F2, or B1:F100, etc)

If there are other requirements, please be more specific.

--ron