Hi,
I have a large column of cells with varying text lengths and I need to check each cell to see if it has the text "Joe Smith" and "Golf Clubs". Basically I want to count how many instances of that combination exists in the entire column.
Any help would be appreciated.
Thanks,
Phillycheese
Last edited by Phillycheese5; 10-04-2010 at 10:25 AM.
To count instances of the combination of those in one cell...
=SUMPRODUCT(--ISNUMBER(SEARCH("Joe Smith",A1:A100)),--ISNUMBER(SEARCH("Golf Clubs",A1:A100)))
in Xl2007 or later
=COUNTIFS(A1:A100,"*Joe Smith*",A1:A100,"*golf clubs*")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
How about this:
=SUMPRODUCT((ISNUMBER(FIND("Joe Smith",A1:A10))*(ISNUMBER(FIND("Golf Clubs",A1:A10)))))
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
or more succinctly
=SUMPRODUCT(1*(ISNUMBER(FIND("Joe Smith",A1:A10)+FIND("Golf Clubs",A1:A10))))
Thank you...works great.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks