Searching and counting in an array. | Y_Elias | 2/11/09 8:47 PM | I'm trying to search and count instances of a letter combination. Here's what I have so far: =counta(FILTER(A1:A ; SEARCH(G1; " "&A1:A&" "))) Cell G1 contains the string I'm looking for. This works great in the A column, but I can't seem to get it to work for an array A1:C Any help would be greatly appreciated. |

Re: Searching and counting in an array. | ahab | 2/12/09 2:09 AM | How about?:
=counta(IFERROR(FILTER(A1:A ; SEARCH(G1; " "&A1:A&" "))))+counta(IFERROR(FILTER(B1:B ; SEARCH(G1; " "&B1:B&" "))))+counta(IFERROR(FILTER(C1:C ; SEARCH(G1; " "&C1:C&" ")))) |

Re: Searching and counting in an array. | Y_Elias | 2/12/09 8:38 AM | That worked well. Thanks. Is there a way to search a 20 column array (A1:T) with a more elegant formula? If not, the aforementioned formula will work fine. Thanks |

Re: Searching and counting in an array. | ahab | 2/12/09 11:26 AM | -- Corrected (2) --
Try this:
=ARRAYFORMULA(COUNTA(FILTER( SPLIT(CONCATENATE( A1:T&CHAR(13));CHAR(13)) ; SEARCH( U1 ; " "&SPLIT(CONCATENATE( A1:T&CHAR(13));CHAR(13))&" "))))
Note: In this formula I put the pattern to look for outside the A1:T range, i.e. in the cell U1 (I bolded this in the formula), to avoid circular reference errors. |

Re: Searching and counting in an array. | ahab | 2/12/09 11:50 AM | Hmm, I have another really more elegant solution: =ARRAYFORMULA(COUNTA(IFERROR(SEARCH( U1; " "&A1:T&" ")))) |

Re: Searching and counting in an array. | Y_Elias | 2/12/09 11:54 AM | WOW!!! That worked really well. AHAB- DOES IT AGAIN!! |