count no. different values depending on another value

Hi,

I am trying to work out a way of counting the number of times a value occurs depending on another value. For example I want to know the number of fathers in a nest but I don't care about the number of times their ID occurs. See below:

FatherID nest
*1 22
*1 22
*2 22
*1 22
*3 22
*1 22
*1 22
*1 22
*1 22
*1 22
*1 22
*1 22
*1 22
*1 22
*1 22
*4 59
*5 59
*4 59
*4 59
*5 59
*4 59
*4 59
*5 59
*6 59
*5 59
*7 83
*8 83
*9 83
*7 83
*8 83
*7 83


So within nest 22 there are 3 fathers and within nest 59 there are 3 fathers and same for nest 83.

Many Thanks,

Rosey

Hi,

I am currently using a pivot table which will give me a list of the number of fathers per nest but I have a very large data set so it is very time consuming to go through the list and effectively count the number manually.

What I would like is two columns: 'nest number' and 'number of fathers'.

I can get the number of each father ID but not the number of different fathers per nest.

Thanks for your post

Once you have the data in a Pivot Table you can use the COUNIFS or SUMIFS formula to calculate the total based on whatver criteria you care to apply.

Thank you!!!

That's very helpful!