counting recurrence another shee

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Counting recurrence in another sheet

Counting recurrence in another sheet

resolvedResolved · Low Priority · Version 365

Lou has attended:
Excel Intermediate course

Counting recurrence in another sheet

Hello! I am trying to count the occurrence of email addresses in a sheet. I would like to create a running count so I can see the number of times each email address gets mentioned. The sheet with the email addresses will be updated over the next few days so the count should continue to grow, and new email addresses might be added.
Please could you advise. Thanks.

RE: counting recurrence in another sheet

Hi Lou,

Thank you for your question about counting emails addresses

Because email addresses are based on text, you will need to use the COUNTA formula. Suppose your email list is in column A, the formula to do a running total would be:

=COUNTA(A:A)

Now when new email addresses are added below the existing list, the total count will always update.

I hope this solves your problem. Please let us know if it does or not.

Kind regards
Martin

RE: counting recurrence in another sheet

Thanks Martin! I think I didn't explain myself clearly enough. I have used CountA and have successfully got a running total of how many email addresses I have added. However, what I want to know is a running total of how many of each email address I have, for example:
- jsmith@email : 5
- ngreg@email : 2 etc. etc.

Is that something I can do? Thanks!

RE: 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

RE: counting recurrence in another sheet

Hi Martin! Thank you!
I've just tried the unique function and selected three column ranges, as I am collating email addresses from 3 columns.

However, it's pulled a lot of VALUE! warnings doing it this way. It seems I can use the Unique function successfully if I use one column at a time, but that adds in extra steps. Any ideas? Thanks!

RE: counting recurrence in another sheet

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


RE: counting recurrence in another sheet

Hi Martin! Thank you!
I've just tried the unique function and selected three column ranges, as I am collating email addresses from 3 columns.

However, it's pulled a lot of VALUE! warnings doing it this way. It seems I can use the Unique function successfully if I use one column at a time, but that adds in extra steps. Any ideas? Thanks!

RE: counting recurrence in another sheet

Hi Martin, thank you!
I am not sure I understand step 2. I have completed step one and have a list of 0's which link to the master sheet.

When you say "Continue creating further links by repeating step 1 so you end of with a single list with zeros in between", do you mean start linking the second column of emails below the 0s which link to the first column?

Thanks!

RE: counting recurrence in another sheet

Hi Martin, thank you!
I am not sure I understand step 2. I have completed step one and have a list of 0's which link to the master sheet.

When you say "Continue creating further links by repeating step 1 so you end of with a single list with zeros in between", do you mean start linking the second column of emails below the 0s which link to the first column?

Thanks!

RE: counting recurrence in another sheet

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

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.


 

Excel tip:

Ctrl+d's double life

Suppose I have a formula in B1 that I wish to copy into B2:B10. I can select B1:B10 then press Ctrl+d to copy the formula down the selected range. Users generally ignore this shortcut in favour of double-clicking on the fill handle to copy down, but Ctrl+d is useful sometimes particularly when there is no data in surrounding columns to guide to how far the double-click method should copy formulae.

Ctrl+d has another use though. When I use the drawing toolbar to draw objects such as Text Boxes, Rectangles and Ovals onto a worksheet, Ctrl+d makes an instant duplicate of selected shapes. For example, I need five Text Boxes the same size. I draw one Text box and adjust it to the size I want, select it, then press Ctrl+d four times to get four identical copies.

View all Excel hints and tips


Server loaded in 0.06 secs.