need help with nested IF AND OR function

[Nbi001]

I'm maintaining a database of students at our school. I'm trying to
count the number of students(rows) within a family. Each record(row)
of data is one student, therefore a family may have more than one
record .....

My formula currently is: IF(D1<>D2,IF(OR D2>D1),1,2)2 and I've copied
it down with relative references.....

I'm looking at one column - the Family ID column - Column "D" below.
If the family ID number is the same for 2 rows, then that family has
more than one student. My problem is that for the second or third
student listed, I want a "0" posted.....

Sample data columns:
D current Formula Result Desired Results
Family ID # in family # in family
1 1 1
2 1 1
3 1 1
4 1 1
5 2 2
5 1 0
6 1 1
7 2 3
7 1 0
7 1 0
8 1 1
etc.

In other words, family #1 has one student at our school, family #5 has
2 students at our school, family #7 has 3 students.

I'm trying to write a formula to determine the number of students in
that family by looking at the Family ID column. My desired results
column is the critical point to all of this - it's called our "Oldest
Or Only" data for our school. I did it manually last year - we have a
very large school and I'm hoping to save some time by NOT doing this
manually.

I've been mired in nested And/Or IF logic for 4 days now - I've tried
numerous sites, message boards...etc. I'm not an advanced user as you
may be able to tell. I don't know VBA.

I know if have some And/Or boolean issues here.... I just can't get it
right.

 

[Frank Kabel]

Hi
in C1 try
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$100,A1),0)
and copy down

 

[Harlan Grove]

in C1 try
=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:$A$100,A1),0)
and copy down

....

If family IDs began in row 2 with a heading in row 1, I'd use

=IF(A2<>A1,COUNTIF(A2:A$101,A2),0)

If family IDs began in row 1, then I'd use a different formula for row 1 and
the subsequent rows.

X1:
=COUNTIF(A1:A100,A1)

X2:
=IF(A2<>A1,COUNTIF(A2:A$100,A2),0)