Count number of times value is present i area.

[bendixboogie]

Hi All.

I have a task where i have to count how of many time values are present in a colum or area.

Ex.:

Colum / Area:

11
22
33
44
55
66
77
88
99
11
11
22
33
33
33
44
88
99
99
99
99


Result should be something like this:

Value Counts
11 3
22 2
33 4
44 2
55 1
66 1
77 1
88 2
99 5


Is there some way this could be done??

Thank you in advance :D

[Azam Ali]

Hi bendixboogie

This could be done thorugh pivot table and thorugh formula as well

Kindly see the enclosed file for both type solutions

[bendixboogie]

Hi bendixboogie

This could be done thorugh pivot table and thorugh formula as well

Kindly see the enclosed file for both type solutions

Hi Azam.

Thank you for your VERY quick reply.

It seems to work fine in the file you attached.

I am a complete novice regarding Pivot Tables, so I have given up on that already

As a formula, I dont really understand the way you have done it. If you could explane it, it would be very helpfull!

Thank you in advance

[Azam Ali]

Hi bendixboogie

The formula in the B column is simple countif which is counting the values of A column from the values in column A of data

The formula in A column is an array formula and executed though the Shift+Ctrl+Entre

The first MATCH is comparing the values in the A column of data with the values in the above cells and giving an array either row number if both match or #N/A if there is no match.

The INDEX(ISNA) is converting #N/A values in TRUE and other Value as FALSE. Than MATCH(TRUE is giving the row number in the data where values in above cells do not match.

The first INDEX is then picking the value form the above row number.

For detail understanding, you could use the Evaluate Formula option of Excel in the Formula Tab or tell me for any further explanation.

The pivot table option is much easier and following site may be helpful for you.

http://www.datapigtechnologies.com/ExcelMain.htm