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).

    spreadsheet with input in Rows 2 to 7 and output in rows 9 to 20

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)

Excel Countif Image

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

More information on using countif can be found here https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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