I have an Excel workbook in which each cell in column B contains a short fable (story). These cells (which are formatted "General") contain text values that are large by spreadsheet standards: all but one is over the 255 character limit for text display, one cell contains more than 3,000 characters, and I guess that most are between 500 and 700 characters. The second illustration below shows such a cell.
In another workbook, I have (meta)data about the fables. In particular, I am interested in frequency of occurrence of some words. In the illustration immediately below, Column C contains a list of the words I am interested in. Column B contains the total number of occurrences of the corresponding word (counting multiple occurrences within a fable); it is irrelevant to this question. I also want to count the number of fables in which each word appears (at least once). I call this "Dispersion" (see explanation in comment below). I have been using Ctrl+F ("Find and Replace") and clicking "Find All", which reports a count of the cells that contain the search string (see second illustration below). I did this one at a time, for each word, and manually entered the numbers into Column N.
(You can probably skip this paragraph.) I need counts for single, whole word instances, and not derived forms of root words (not even plurals). For example, my count for "animal" needs to return counts for just "animal," not "animales" or any other such variation. Early on, I realized that simply searching for a word could lead to a false count because it would include words that contained the word I was searching for. I got around this by padding my search terms with spaces at the beginning and end – in Column E (e.g., " animal "), which contains =" "&C2&" " – and ensuring that the column against which I am checking these words also isolated such words. Wherever a punctuation mark fell adjacent to the last or first letter in a word in a fable, I inserted a space to eliminate any such adjacency. For example, "todo esto, porque siendo" became "todo esto , porque siendo". (This was partially inspired by JNevill's comment on this question: Excel's COUNTIF doesn't work.) My Ctrl+F searches then returned for me counts for single instances of the words I searched for.
Of course this is tedious, time consuming and error-prone, so I got to wondering if some formula wouldn't be able to do the same thing, but faster. A couple of posts/web pages suggested that the COUNTIF function might be able to accomplish this, so I've been experimenting with it, but thus far, my attempts have failed every time. In the above illustration, Column M contains
=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2)
and as you can see, it is displaying a "0" when it should be matching the number in Column N (i.e., the count found using Ctrl+F). (As stated above, Column E contains the string I am looking for, which is the word I am looking for, padded with spaces at the beginning and end.) Every configuration using the COUNTIF function I have tried has failed.
Below is an image of cell B23 in my fables worksheet (i.e., one fable). The view is partially obstructed by a "Find and Replace" dialog box, showing that 13 cells (fables) contain the word "animal" (i.e., they contain the string " animal ", with leading and trailing spaces) at least once.
(You may need to enlarge your view of the image to see this clearly.)
The last thing I'm going to show you, is what happens when I use the COUNTIF function. I think it may be key to why I can't get this to work, because as soon as I add the range from the needed column, before I even press Enter, I see #VALUE!; #VALUE!; … next to the range. It looks like this:
When I press Enter, I get the "0" you see for the cells in that column.
What am I doing wrong? How can I count the number of fables in which each word appears (at least once)?
If you can't tell from the screenshots, I'm using Excel 2007.
Answer
@fixer1234 is right — COUNTIF counts the cells that are equal to a value, not cells that contain a string. For that, you need to use FIND or SEARCH. (They are identical, except FIND is case-sensitive and SEARCH is case-insensitive. I’ll just assume that you want the case-insensitive one.)
Start by doing
=SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)
This will look for the value of E2 (in your example, “ animal ”) in cell B1 of the other worksheet. If that string value is present in that cell, this will return the location of the first occurrence of the search string in the cell’s text (with the first character being 1). If the string is not present, it will return #VALUE!.
Next, do
=IF(ISERROR(SEARCH(E$2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)), 0, 1)
This will evaluate to 1 if the string is present and 0 if it is not. The next step is:
=SUM(IF(ISERROR(SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))
This sums the previous formula along column B of the other worksheet, giving you the count that you want. Note that the above is an array formula. This means that, to get it to work, you must type Ctrl+Shift+Enter after you type the formula. Now you can put this into cell M2 and drag down.
You don’t really need to have column E — you can handle it within your SEARCH formula:
=SUM(IF(ISERROR(SEARCH(" "&C2&" ", '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))
I tested this in Excel 2013, but I’ve done things like this before, and I expect that this solution will work in Excel 2007. (And I tested with cells with more than 750 characters, and with a workbook file name that contains a space.)
P.S. I don’t know why you got those #VALUE! errors in the “Function Arguments” dialog; it worked for me:
(I tested it even though my answer doesn’t use COUNTIF.) Do you have the other workbook open while you’re doing this?
No comments:
Post a Comment