# How to use Countif function or any other formula to sort YEAR . Country And any occurrenc!

1. ## How to use Countif function or any other formula to sort YEAR . Country And any occurrenc!

My Data looks like this:

Year Country
2000 Afghanistan
2000 Afghanistan
2000 India
2000 Malaysia
2001 Afghanistan
2001 Afghanistan
2001 India
2001 India
2001 India

What I need to do is to sort data like this:

Year Country Occurrence
2000 Afghanistan 2
2000 India 1
2000 Malaysia 1
2001 Afghanistan 2
2001 India 3

the occurrence is basically how many times a year has been repeated in the data-set. What is the best formula for this? Many thanks in Advance.

2. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

It would help if you attached a sample Excel workbook, as no-one likes to spend time entering your sample data into Excel before being able to try out a few ideas.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

3. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

i prefer Pivot Tables. Data must be uniform (Ex India with a space at the end is different than India without a space). In pivot table: Column = Year; Rows = Country and Values = Count of Country. This will give you a nice summarized table

4. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

Hi Aijaz,

I agree with queuesef - please see Pivot Table attached.

Regards

peterrc

5. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

Hello aijaz75. Welcome to the forum.

Try copy/paste data in column A:B to column D:E. Apply Remove Duplicates feature. Then this formula in F2 and fill down.
Formula:
`Please Login or Register  to view this content.`

 A B C D E F 1 Year Country Year Country 2 2000 Afghanistan 2000 Afghanistan 2 3 2000 Afghanistan 2000 India 1 4 2000 India 2000 Malaysia 1 5 2000 Malaysia 2001 Afghanistan 2 6 2001 Afghanistan 2001 India 3 7 2001 Afghanistan 8 2001 India 9 2001 India 10 2001 India

6. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

Many thanks all of you. It really helped.

7. ## Re: How to use Countif function or any other formula to sort YEAR . Country And any occurr

You're welcome. Glad to help. Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1