How to get a frequency count when first and last names are in dif columns in excel

Question

I have an Excel 2010 workbook with one row for Last name and one row for first name. There are approximately 1800 entries.

The same person (first and last name) can appear multiple times- jim simith appears 5 times. I want to do a frequency count of how many times jim simth appears in the list.

I used =COUNTIF($B$2:$B$1800,B2) , where B is the last name. The problem is that there are other Smiths in the list that are counted because the first name is in a separate column. Does anyone know how I could do a frequency count of each individual where their last names can be the same?

You could just use a pivot table – lc. Aug 30 '13 at 03:36 — lc., Aug 30 '13 at 03:36

Rick · Answer 1 · 2013-08-30T04:00:09.277

2

=COUNTIFS($B$2:$B$1800,B2,$A$2:$A$1800,A2)

edited Aug 30 '13 at 04:00

answered Aug 30 '13 at 03:49

Rick

1,063
8
26

This will only work for Excel 2007+. For Excel 2003 and lower, use `=SUMPRODUCT(($B$2:$B$1800=B2)*($A$2:$A$1800=A2))` – jmac Aug 30 '13 at 03:53
This is true, however op specifically said it was a 2010 workbook – Rick Aug 30 '13 at 03:54
Yes he did -- it just wasn't tagged 2010, and so I wanted to make sure that someone who tries it with a different version doesn't get confused. – jmac Aug 30 '13 at 04:01
HI, this works but I found a new issue- one I dont believ can be solved without some natural language code. I have the same person with two different spellings of the first name or an added middle initial in the first name column. I think some data cleaning is in order. Thanks. – user2731437 Aug 31 '13 at 02:57

score 0 · Answer 2 · answered Aug 30 '13 at 03:50

0

I haven't tested this, and I'm assuming your COUNTIF formula works.

In a new column, you can CONCATENATE the strings in the columns and run your COUNTIF on that.

Assuming that A is first name and B is last name, in C you'd have something like:

=CONCATENATE(A2, " ", B2)

And then you'd do the COUNTIF on that:

=COUNTIF($C2:$C1800,$C2)

Optionally, you may hide column C if you don't want it displayed.

answered Aug 30 '13 at 03:50

rink.attendant.6

44,500
61
101
156

@Rick I wasn't aware of the `&` operator in Excel, thanks for sharing! :) – rink.attendant.6 Aug 30 '13 at 03:53
You are welcome, I used concatenate for years before I realised you could just use `&` – Rick Aug 30 '13 at 03:59
Thanks. This worked as well but I have the same issue as I noted above in the first answer. Thanks again. – user2731437 Aug 31 '13 at 03:00

score 0 · Answer 3 · edited Aug 30 '13 at 05:36

Probably overkill, but you can create an ADODB connection to the worksheet:

Dim conn As New Connection
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""" & ActiveWorkbook.FullName & """;" & _
        "Extended Properties=""Excel 12.0;HDR=No;"""
    'If you're running a version of Excel earlier than 2007, the connection string should look like this:
    '.ConnectionString = "Data Source=""" & ActiveWorkbook.FullName & """;" & _
    '    "Extended Properties=""Excel 8.0;HDR=No;"""
    .Open
End With

Then you can issue an SQL statement against the worksheet:

Dim rs As Recordset
Set rs = conn.Execute( _
    "SELECT F2 AS LastName, F1 AS FirstName, Count(*) AS C " & _
    "FROM [Sheet1$A$2:$B$1800] " & _
    "GROUP BY F2, F1" _
)

Thanks for the answer. This is a bit beyond my skill level. – user2731437 Aug 31 '13 at 02:58 — user2731437, Aug 31 '13 at 02:58

How to get a frequency count when first and last names are in dif columns in excel

3 Answers3