|
Colomn of strings, how to count each string with "abc" OR "def"
I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy, please help! |
Tommy T Wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! Hi Tommy Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your data, change abc to def to count the other option -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401899 |
Thanks Ron,
I'd managed to get that far but the trouble is that I need the cell to be counted if there is "abc" OR "def" within it. It's the OR bit which is causing me trouble. Any ideas? Tom "Ron Rosenfeld" wrote: On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! =COUNTIF(A:A,"*abc*") or, if your string to search for is in a cell: =COUNTIF(A:A,"*"&D2&"*") --ron |
Thanks Paul,
Trouble is that some of the cells contain both phrases, if I have two different countif functions then they get double counted which I'm trying to avoid. Tom "Paul Sheppard" wrote: Tommy T Wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! Hi Tommy Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your data, change abc to def to count the other option -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401899 |
Tommy T Wrote: Thanks Ron, I'd managed to get that far but the trouble is that I need the cell to be counted if there is "abc" OR "def" within it. It's the OR bit which is causing me trouble. Any ideas? Hi Tommy Try this =SUM((COUNTIF(A:A,"*abc*")+(COUNTIF(A:A,"*def*"))) ) -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401899 |
Try...
=SUMPRODUCT(--(ISNUMBER(SEARCH("abc",A1:A100))+ISNUMBER(SEARCH(" def",A1:A 100))0)) or =SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A100))+ISNUMBER(SEARCH(C1,A 1:A100)) 0)) ....where B1 contains your first criterion, such as abc, and C1 contains your second criterion, such as def. Hope this helps! In article , "Tommy T" <Tommy wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! |
Tommy T Wrote: Thanks Paul, Trouble is that some of the cells contain both phrases, if I have two different countif functions then they get double counted which I'm trying to avoid. Tom "Paul Sheppard" wrote: Tommy T Wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! Hi Tommy Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your data, change abc to def to count the other option -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401899 Hi Tommy Try Data Filter Autofilter Custom filter contains abc or contains def -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=401899 |
On Mon, 5 Sep 2005 04:46:01 -0700, "Tommy T"
wrote: Thanks Ron, I'd managed to get that far but the trouble is that I need the cell to be counted if there is "abc" OR "def" within it. It's the OR bit which is causing me trouble. Any ideas? Tom "Ron Rosenfeld" wrote: On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy wrote: I have a colomn of alpha-numeric strings and need to count cells which contain phrase A or phrase B buried within them. This is driving me crazy, please help! =COUNTIF(A:A,"*abc*") or, if your string to search for is in a cell: =COUNTIF(A:A,"*"&D2&"*") --ron =SUM(COUNTIF(A:A,{"*abc*","*def*"})) will count each cell that has one or the other string. However, it will double count cells that have both strings: e.g. 12abc34def56 would get counted twice. If you don't want double counting, then: =SUM(COUNTIF(A:A,{"*abc*","*def*"}),-COUNTIF(A:A,"*abc*def*"),-COUNTIF(A:A,"*def*abc*")) --ron |
| All times are GMT +1. The time now is 03:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com