Counting repeats in name lists

I send out a newsletter every month and I end up with a list of people who've opened it. Each month I'd like to see who came back.

Basically the sort field is an email address, so I'll have a number of columns (one for each month) and I'd like to count how many people come back and how many times.

-

2 Answers

If your interested in a suggestion for a different approach, here's what I'd do:

First, I'd put all the data in two columns: one for email and one for date. This type of more "normalized" data is easier to analyze, especially as you get more over time. In the example below, I put it in an Excel table.

Then, I'd create a pivot table based on the table you just made. I'd set it up like in the picture. One thing to note is I right-clicked on the date columns and grouped them by year and month.

If you've never used pivot tables, they take some learning, but once you know how to use them they are very powerful tools for counting and summing things by their various characteristics:

-

So, each column is a month, and in each column there are the emails of who opened the email?

If you send the emails out to the same group of people each month, I would just use COUNTIF. So, for instance, add a column after the last one that contains all the emails you've sent the messages to, and then do a COUNTIF on the range containing the emails.

Like this:

``````    A          B          C          D            E            F
1 Month1     Month2     Month3     Month4       Emails      Formula
2 a@z.com    b@z.com    a@z.com    a@z.com      a@z.com     =COUNTIF(A2:D7,E2)
3 b@z.com    c@z.com    b@z.com    c@z.com      b@z.com     =COUNTIF(A2:D7,E3)
4 c@z.com    e@z.com    d@z.com    d@z.com      c@z.com     =COUNTIF(A2:D7,E4)
5 d@z.com               f@z.com    e@z.com      d@z.com     =COUNTIF(A2:D7,E5)
6                                               e@z.com     =COUNTIF(A2:D7,E6)
7                                               f@z.com     =COUNTIF(A2:D7,E7)
``````

The same basic principle can apply to finding out how many answered on a specific month; just change the range in the COUNTIF from A2:D7 (or whatever) to A2:A7, B2:B7, and so on.

The only downside here is that you need to keep track of all the emails that you've used for newsletters, so you can put them in Column E. However, hopefully that's not too hard, since you can just copy and paste them from the email you sent. If you end up with duplicates, you can use the "remove duplicates" feature in Excel. HTH!

-