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

[aijaz75]

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.

[Pete_UK]

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

[queuesef]

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

[peterrc]

Hi Aijaz,

I agree with queuesef - please see Pivot Table attached.

Regards

peterrc

[FlameRetired]

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.

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

[aijaz75]

Many thanks all of you. It really helped.

[FlameRetired]

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.