Counting recurrence in another sheet

Hi Lou,

Thank you for your reply.

I understand now what you want to achieve.

Say you had a list of email addresses in column A from A2:A200. You can create a running total of each email address using the COUNTIF function. But before this is created, I would firstly create a unique list from your email address list using the UNIQUE function (only available in Office 365). Here's how to do it:

Say you had 20 unique email addresses which appeared numerous times in the main list

1. In a blank cell (say D2) type =UNIQUE(A2:A200). NB. make sure you have enough space of at least 20 blank cells below or you will get a SPILL error. If you get this message just delete any data in the way to fix it

2. Now you have the unique list, select cell E2 and type =COUNTIF(A:A,D2) where A:A is the list you are interested in and D2 is the criteria applied to that list

3. Copy down the formula to get the other results

4. Now when you add more email addresses to the main list (that have been added before) your results will automatically update

I hope this helps. Please let me know if this has solved your problem or not

Kind regards
Martin

Hi Lou,

Thank you for your follow up question.

So your email addresses are split over 3 columns. The UNIQUE function can only work for a single column of data entries as you rightly say. To remedy this I suggest you do the following:

1. Create a link cell on another sheet to the first email address on sheet 1. Then copy down the link beyond the point where all the emails are populated. This will produce a list of zeros which can be dealt with later

2. Continue creating further links by repeating step 1 so you end of with a single list with zeros in between

3. Say the linked list is in column A starting in A2. In B2 type =IF(A2=0,"",A2) which will return the list again but now you will get blank cells instead of zeros. Say the list is in cells B2:B300

4. Now in C2 type =UNIQUE(B2:B300)

5. In D2 type =COUNTIF($B$2:$B$300,C2)

6. Copy down the formula to get a count of each individual email address

7. Now if you add an existing or even new email address in any of the original 3 lists, this will automatically be picked up in the email count

Notice that the unique list will pick up a blank and the email count will then pick up how many blanks in the other list.

I hope this resolves your issue. Please let me know if has worked or not.

Kind regards
Martin

Hi Lou,

Yes that's what I mean. Your list should look like this linked to the first list:


email 1
email 2
email 3
email 4
email 5
0
0
0
0

Then select the cell directly below the last 0 and link to the top of the 2nd list and copy down. Repeat for list 3. So you should get something like this:

email 1
email 2
email 3
email 4
email 5
0
0
0
0
email 1
email 2
email 2
email 4
email 3
0
0
0
0
email 5
email 4
email 3
email 4
email 5
0
0
0
0


Hope this makes sense

Kind regards
Martin