List how many times an item appears in a category in another column

[caranpaima]

Hello everyone

After giving it a good thought for several days and looking around in this and other EXCEL forums, I finally gave up on finding out on my own on how to do the following operation (tried several countifs and sumproduct variations but could not get it to work):

Let´s say I have a list like the following two columns:

Block Species
1A Jaguar
1A Tucan
1B Iguana
1A Jaguar
1C Tucan
1D Iguana
1A Iguana
1D Iguana
1B Parrot
.... and another 15000 similar rows


Is there any way to calculate how many times did any of the species occur in the blocks? For instance, a result would be:

Species Number of Blocks
Jaguar 5
Parrot 7
Iguana 10

It is seemingly simple to do, and so far I am doing it with pivot table stuff, but would love it to have a formula for this. Stumped so far . Any help would be welcome!

 

[jeffreybrown]

x-post

 

[Marcol]

Cross posted

Some ideas and a possible solution posted at Excel Forum
http://www.excelforum.com/excel-mis...a-category-in-another-column.html#post2567227