Categories: Ask a "how-to" question :

Searching and counting in an array.

Showing 1-6 of 6 messages
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!!