Here is one way:
the first two rows are headers:
B3=IF(IFERROR(SEARCH(B$2,$A3,1), 0)>0, 1, 0)
select B3 and fill to right as needed, select B3 through the end of the row, then fill down as needed
select B1 and fill to the right as needed
Place the names in row 2 as shown. The main weakness of this solution is it will not count a name if it ocurrs twice in the same sentence.
Here's a suggestion similar to Wayne's, using a formula that will count multiple occurrences of a name in the same cell/sentence.
Fill right and down as directed in Wayne's post.
Note the counts of Bob and Sam in rows 6 and 7.
SUBSTITUTE does not distinguish between "Bob" as a word and "Bob" as part of a larger word ( "Bobsled" ), so it counts both occurrences of the string "Bob" in row 6.
SUBSTITUTE is case sensitive, though, and does not count "bob" in "bobsled" (in row 7) as an occurrence of "Bob" or "sam" in "sample" (in rows 6 and 7) as occurrences of "Sam".
SEARCH is insensitive to case. Note the counts for "Bob", "Sam" and "Jack" in the same two rows, using the SEARCH based formula in Wayne's post:
this is the simplies for what i am looking for.
I wish there was an easier way to generate the total...
for my purpose i tweeked your formula.
I needed to give the characters in my film each a number, so instead of returning a one, i am returning numbers 1-22.
and i am funnel all my search results into one cell using the "&" sign inbetween each formula so i get an entire column with something like this...
1 3 11 22
showing that character 1,3,11, and 22 appear in that specific shot...
is there a way to count "1"s in that entire column and the "2"s? and so on.
Number your characters from 11 to 32 so that each is represented by an unique two character string in the column. Since you want to count only if the character is present in that scene, you need each two character string to appear once in the list cell without regard to the number of times the name or number appears in the line, or you need a counting method that counts only whether the character's string appears in each line of the table.
The sample below uses the second method; the character's two digit identifier appears as many times as the character appears in the scene, but is counted only once.
The character lists were generated randomly, with no control over multiple appearances on the same line (see row 13, for example). The formula counts only the first appearance in the line.
E2, and filled down: =COUNTIF(B,"=*"&D2&"*")
If the name appears only once in each line, a simple formula to count how many lines contain the specified name is as follows:
or if the name is specified in a cell, like cell B1,
Those two formulas do not distiguish between the name "Bob" and words that contain "bob" such as "bobsled". You can fix this pretty easily though with several countifs added together. One will look for the name at the beginning of a sentence. One will look for the name in the middle of a sentence. And one will look for the name at the end of a sentence. For that last one, the sentences must all end in a period (as assumed in the formula below) or no period (you'll have to modify the formula accordingly).
=COUNTIF(A,"="&B1&" *")+COUNTIF(A,"=* "&B1&" *")+COUNTIF(A,"=* "&B1&".")
This formula will count all instances of the name, including multiple instances on the same line.
The "*" is a wild card character.
Each of the three formulas posted in the messages by me and by badunit uses the wildcard in a similar manner:
- =COUNTIF(A,"="&B1&" *")+COUNTIF(A,"=* "&B1&" *")+COUNTIF(A,"=* "&B1&".")
The first says: Count the cells in column A containing the string "Bob" preceded and/or followed by any other characters.'
The second says: Count the cells in column B containing any characters followed by the contents of cell D2 followed by any characters.
The third says:
Count the cells in column A that start with the contents of B1 followed by a space;
Add the count of all the cells that contain any characters ending with a space followed by the contents of B1 followed by any other characters starting with a space;
Add the count of all the cells that contain any characters ending with a space followed by the contents of B1 followed by a period.
The first two will count each cell meeting the condition only once.
The third will count a cell once for each of the condition it meets, to a maximum of three (one for each of the three conditions).
Formula #3. the first Bob fits the first condition; the second Bob and the third Bob both fit the second condition, but are counted only once; the fourth Bob fits the third condition and is counted for a total of three.
The ampersand in the formulas is the concatenation operator. It's function is to join (concatenate) two text strings into one, as noted by "followed by" in the descriptions. "Any character" includes "no characters," so the first two formulas will count the cell if "Bob" (or the contents of the addressed cell) are found anywhere in the string contained in that cell.
PS: "=*Bob*" would count a cell containing "Jim-Bob", "four-man Bobsled", "Bobby" or "the Bob-tail nag."