1

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.

  • 1
    Just a guess, but the countif criteria counts exact matches at the cell level. It doesn't operate like a "contains" function. Search works because it does operate on a "contains" basis. As an aside, col N doesn't match col B. If that's important and already described, I missed it because the question is pretty long. – fixer1234 Oct 9 '16 at 0:26
  • Your question could benefit from more explanation. I don’t know what you mean by “Dispersion”, and I don’t see any one place that concisely describes what you’re trying to do. (I believe that I’ve pieced it together.) – Scott Oct 9 '16 at 9:42
  • @fixer1234 You've got a sharp eye. You're right, N does not match B. B can contain more than N or match it but it cannot exceed it. Each cell in the one spreadsheet I have contains a fable and that is what I am now trying to get a good count for. B represents the total number of times the word appears. Some words appear more than once in a fable. – Lisa Beck Oct 13 '16 at 19:06
  • @Scott I really felt the more minute details went a little too deep for most, but if my short column names are really of interest and you think it would help, I'm really just trying to get the frequency and breadth of words in a set of documents. Frequency is just that -- the number of times the word appears in a set of documents; breadth refers to how many of the documents the word appears in. I use "dispersion" instead of "breadth" simply because "dispersion" seems like a more precise word to me than "breadth." Does that help? – Lisa Beck Oct 13 '16 at 19:09
  • I've come up with an analogy that may help others understand this better and perhaps use any formula(s) that generate from this post for other purposes. Let's say you want to examine how safe drivers are in various metropolitan areas. Naturally, you'd want to collect data not just on the frequency of traffic accidents (the total number) but also the dispersion or breadth of such accidents -- on how many different streets/at how many different intersections, et alia. – Lisa Beck Oct 13 '16 at 19:23
2

@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:

“Function Arguments” dialog

(I tested it even though my answer doesn’t use COUNTIF.)  Do you have the other workbook open while you’re doing this?

| improve this answer | |
  • Really looking forward to experimenting with your suggestion. Before I do so, I'll address a couple of things really quickly here. For starters, yes, your assumption that I would need to use a case insensitive search was correct. And to address the question you posed at the end, yes, I have the other workbook open when I attempt to experiment with various formulas. Before I leave to experiment with yours, thank you for taking the time to so thoroughly test this out and so clearly answer this. Even if the formula doesn't wind up working for me, h/t to you for the quality of your answer. – Lisa Beck Oct 13 '16 at 19:37
  • I'll admit that I was a bit skeptical that this would work after I completed the first step, but I kept faith and carried it through. I haven't done one of those uber scrupulous checks, but a quick, spot check indicates your formula worked like a charm. Absolutely brilliant, @Scott. It is easy to give you the checkmark for this. – Lisa Beck Oct 13 '16 at 20:37
  • I'm glad I could help. – Scott Oct 13 '16 at 20:41
0

I'm having some trouble seeing your screenshots, but since you're trying to count the output of other functions, I'd try using the value() function within your countif statement. Value() will tell excel to look at the output (what you see in the cells) rather than the underlying function that you've written into them.

Your countif statement would look something like "=countif(value([other worksheet]range),criteria)"

| improve this answer | |
  • Interesting answer, although somewhat contradictory to what others have claimed to be limitations of the COUNTIF function. If I am misinterpreting something here, please don't be offended. I'm not trying to be rude ... just simply expressing an observation. I appreciate the fact that you attempted to answer this and should my experimentation with Scott's suggestion not work, I'll definitely give yours a shot. Heck, I may even experiment with yours as well even if it does work. Thanks again for suggesting it. – Lisa Beck Oct 13 '16 at 19:43
  • I'll admit that I didn't try too many variations of your formula to see if I could get it to work, but the one message I consistently received after attempting it was that I had too many arguments in the formula. Though your suggestion did not work for me, thank you for making the effort to try to help me with this. – Lisa Beck Oct 13 '16 at 20:41
0

If the long string is in B2, and the word you are looking for is in C2, then the following formula gives the count of how many times the word is in the string

  • =+IFERROR((LEN(B2)-LEN(SUBSTITUTE(B2,C2,"")))/LEN(C2),"")

The parts are

  1. Iferror (optional) makes it blank if there is an error
  2. Len(b2) length of the original cell
  3. LEN(SUBSTITUTE(B2,C2,"") deletes all occurrences of the pattern. This will be shorter if it is in the data
  4. Len(c2) length of the pattern if 6 characters long and there are 3 occurances then 18/6 will result in a 3
| improve this answer | |
0

You can use COUNTIF() to count whether or not one string contains another by exploiting the use of wildcards, as provided by Microsoft. The asterisk (*) matches zero or more characters, while the question mark (?) matches a single character.

So, instead of =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2) use =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,"*"&E2&"*").

The asterisks allow any other text before and after your string. Since your string already has leading and trailing spaces, there should be no trouble with plurals and other forms of words.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.