# Excel: Number of occurrences in a table

In an Excel worksheet, I have a rectangular region (`C3:G7` in the image below) that contains people’s names (e.g., Paul, John, Frank, etc.)  I want to

1. Create a unique (de-duped) linear list of names (in Column `B`; `B9:B20` in the image below, but the number of rows depends on the number of unique names in the input range).
2. In Column `C` (`C9:C20`), count the number of times each name appears in the input.  (This is easy to do with `COUNTIF`.)
3. Sort the output list (`B9:C20`) by count (descending).

To reiterate: rows 2 to 7 are the input (`B3:B7` can be ignored) and rows 9 to 20 are the output.  I’m stuck on the first step (generating the de-duped list of names) and I could use some help on the third step (sorting the output) too.

The COUNTIF function in Excel counts the number of cells in a range that match the supplied criteria

range - The range of cells to count. in the example C3 to G7 this range needs to be locked (i.e. row and column preceded by dollar sign) so when you drag the cell down the range stays the same.

criteria - The criteria that controls which cells should be counted. here counting "Chris" which is in cell B9.

so the formula required in cell C9 is

``````=COUNTIF(\$C\$3:\$G\$7,B9)
``````

Select cell C9 then hover over the bottom right hand side of the cell C9 until the Cross hair appears then double click to populate the formula down to cell C20

EDIT to allow for edited question

To sort the unique names you will need helper columns

In Cell J3 add the following code (note as indicated by the curly brackets this is entered as an array formula i.e. use Ctrl+Shift+Enter) and then drag down to populate the unique names list.

``````{=INDIRECT(TEXT(MIN(IF((\$C\$3:\$G\$7<>"")*(COUNTIF(\$J\$2:J2,\$C\$3:\$G\$7)=0),ROW(\$3:\$7)*100+COLUMN(\$C:\$G),7^8)),"R0C00"),)&""}
``````

Apply a countif in K3 as before with same range but this time referencing the adjacent unique name cell reference J3 then drag down.

``````=COUNTIF(\$C\$3:\$G\$7,J3)
``````

Now rank the unique names based on their helper count column by using the following formula in I3 and drag down

``````=RANK(K3,\$K\$3:\$K\$14,0)+COUNTIF(\$K3:K\$14,K3)-1
``````

Add rank position numbers 1 to 12 in A10 to A21 then use VLOOKUPs to match the position number to the ranking in the helper column and return the "Name" and Count respectively.

for B10 enter

``````=VLOOKUP(\$A10,\$I\$3:\$K\$14,2,FALSE)
``````

and C10

``````=VLOOKUP(\$A10,\$I\$3:\$K\$14,3,FALSE)
``````

Highlight B10 and C10 then drag down to finish.

• Answer edited to allow for question edits. – Antony Nov 28 '18 at 17:37