The first difficulty you'll run into is that there is no "Row A", nor does "LANE" appear at all in column A.
John's formula ( =COUNTIF(A1:N1, "LANE") ) needs its cell range reference moved down to row 3 (the first row on which the MDs' names are listed. With the data shown on your sample (and limiting the "row" to those columns that are visible) the formula(s) will return these counts, correct, but not, I think, what you're expecting:
=COUNTIF(B3:H3, "LANE") 0
=COUNTIF(B4:H4, "LANE") 0
...
=COUNTIF(B11:H11, "LANE") 0
=COUNTIF(B12:H12, "LANE") 3
COUNTIF, and COUNTIFS, take the contition(s) quite literally. There are three cells on row 12 that contain exactly what's specified in the condition—the four letter string LANE. The single cell in row three and the similar cell in row 4 are not counted as they contain an additional five characters.
Enter COUNTIFS, and a rewrite of the condition(s). Here's an example. Note that the count error is deliberate (and explained below)
Row 2 of Data contains the entries we are counting.
Column A of Summary contains the names to be counted. Note that the names must be in ascending alphabetical order, and the list must include a 'cap' (ZZZ)
Column B contains the following formula:
B2: =COUNTIFS(Data :: $2:$2,">="&A2,Data :: $2:$2,"<"&A3)
Fill the formula down to the row with the last name.
COUNTIFS will include an item in the count only if all of the conditions are met. In this case, there are two conditions:
The 'name' must be greater than or equal to the text in cell A2.
AND
the 'name' must be less than the text in cell A3.
For text values, "equal to" means "exactly the same" (disregarding upper and lower case), "less than" means "earlier in an alphabetical sort", and "greater than" means "later in an alphabetical sort."
All of the names on Data are 'greater than' Jones, none are 'less than' Lane, so the count for Jones is zero.
One name on Data (LANE) is 'equal to' Lane, and the rest are greater, only three are also 'less than' Morris, so the count for Lane is 3.
The count for Smith is wrong (as in not the count that is wanted), but is correct for the specified conditions. I left the error there to demonstrate the necessity of including in the list every name that appears in the schedule.
Thanks for the opportunity for practice in writing conditions. It takes a while to get the hang of it. 😉
Regards,
Barry
