countif, again
My original question:
I have a countif formula (see post: variation on countif) that reads Countif(A1:A100,"*value*"). The situation is that users were asked to list their past isp's and could list more than one in a cell. So, is there a way to say "Count this cell if the cell includes either 'aol', 'america online', or 'america on-line'"? The reply: one way: just add the COUNTIF statements. Another way: =SUM(COUNTIF(A1:A100,{"*aol*","*America Online*","*america on-lin*"})) Next question: The only thing is, I want to make sure that people who replied with something like "america online (aol)" don't get counted twice. That's the reason behind this question. Thanks, Liz |
Hi
try: =SUMPRODUCT(--(ISNUMBER(SEARCH("aol",A1:A100))+ISNUMBER(SEARCH(" America Online",A1:A100))+ISNUMBER(SEARCH("america on-line",A1:A100))0)) -- Regards Frank Kabel Frankfurt, Germany Liz G wrote: My original question: I have a countif formula (see post: variation on countif) that reads Countif(A1:A100,"*value*"). The situation is that users were asked to list their past isp's and could list more than one in a cell. So, is there a way to say "Count this cell if the cell includes either 'aol', 'america online', or 'america on-line'"? The reply: one way: just add the COUNTIF statements. Another way: =SUM(COUNTIF(A1:A100,{"*aol*","*America Online*","*america on-lin*"})) Next question: The only thing is, I want to make sure that people who replied with something like "america online (aol)" don't get counted twice. That's the reason behind this question. Thanks, Liz |
You could subtract the count for cells that contain "(aol)" -- with the
parentheses. On Mon, 1 Nov 2004 16:23:42 -0500, "Liz G" wrote: My original question: I have a countif formula (see post: variation on countif) that reads Countif(A1:A100,"*value*"). The situation is that users were asked to list their past isp's and could list more than one in a cell. So, is there a way to say "Count this cell if the cell includes either 'aol', 'america online', or 'america on-line'"? The reply: one way: just add the COUNTIF statements. Another way: =SUM(COUNTIF(A1:A100,{"*aol*","*America Online*","*america on-lin*"})) Next question: The only thing is, I want to make sure that people who replied with something like "america online (aol)" don't get counted twice. That's the reason behind this question. Thanks, Liz |
All times are GMT +1. The time now is 02:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com