Column A contained a unique identifier for each record. I want to be able to
count the number of records with 0's, if a date is also recorded for the same
unique number. Ex: A1, A2, A3 is record 960. A1 has a date, A2 and A3 are
0's. The answer I am looking for would be "2". A8 and A9 is 522. The answer
would be 1.

In the end the final number I am looking for is the sum of 2 and 1, "3".

A B
1 960 7/22/2004
2 960 0
3 960 0
4 967 0
5 990 8/7/2008
6 12 2/6/2007
7 43 10/16/2008
8 522 8/29/2008
9 522 0
10 174 2/28/2007

Put your unique nos in Col C
then enter this in D1 and copy down
=IF(SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$1000))0,SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0)),0)

Adjust 100 to the end of your data set.

"force530" wrote:

Column A contained a unique identifier for each record. I want to be able to
count the number of records with 0's, if a date is also recorded for the same
unique number. Ex: A1, A2, A3 is record 960. A1 has a date, A2 and A3 are
0's. The answer I am looking for would be "2". A8 and A9 is 522. The answer
would be 1.

In the end the final number I am looking for is the sum of 2 and 1, "3".

A B
1 960 7/22/2004
2 960 0
3 960 0
4 967 0
5 990 8/7/2008
6 12 2/6/2007
7 43 10/16/2008
8 522 8/29/2008
9 522 0
10 174 2/28/2007

Simplify version:

=SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0))


"Sheeloo" wrote:

Put your unique nos in Col C
then enter this in D1 and copy down
=IF(SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$1000))0,SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0)),0)

Adjust 100 to the end of your data set.

"force530" wrote:

Column A contained a unique identifier for each record. I want to be able to
count the number of records with 0's, if a date is also recorded for the same
unique number. Ex: A1, A2, A3 is record 960. A1 has a date, A2 and A3 are
0's. The answer I am looking for would be "2". A8 and A9 is 522. The answer
would be 1.

In the end the final number I am looking for is the sum of 2 and 1, "3".

A B
1 960 7/22/2004
2 960 0
3 960 0
4 967 0
5 990 8/7/2008
6 12 2/6/2007
7 43 10/16/2008
8 522 8/29/2008
9 522 0
10 174 2/28/2007

Sorry, I cant get either to work. It must be my explanation.

In Column A, record "960" appears 3 times. In column B, there is a date and
2 zeros next to record 960 on different rows. Since there is a date next to
at least one of these records, I want to count the number of zeros, in this
case it would be "2".

Now, there are more than 26000 entries in this file. I want to count the
number of zeros based on the above condition for each unique record. It maybe
a sort or pivot table issue. I dont know, I have tried both.

"Teethless mama" wrote:

Simplify version:

=SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0))


"Sheeloo" wrote:

Put your unique nos in Col C
then enter this in D1 and copy down
=IF(SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$1000))0,SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0)),0)

Adjust 100 to the end of your data set.

"force530" wrote:

Column A contained a unique identifier for each record. I want to be able to
count the number of records with 0's, if a date is also recorded for the same
unique number. Ex: A1, A2, A3 is record 960. A1 has a date, A2 and A3 are
0's. The answer I am looking for would be "2". A8 and A9 is 522. The answer
would be 1.

In the end the final number I am looking for is the sum of 2 and 1, "3".

A B
1 960 7/22/2004
2 960 0
3 960 0
4 967 0
5 990 8/7/2008
6 12 2/6/2007
7 43 10/16/2008
8 522 8/29/2008
9 522 0
10 174 2/28/2007

Put 960 in C1 and this in D1
=IF(SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$1000))0,SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0)),0)

Change 100 to the last row no of your range
Put other unique nos in C2, C3,,, and copy the above formula down
(You can do Data|Filter|Advance Filter and choose Unique Records and
destination as C1 to get them all at once)
First SUMPRODUCT above counts all occurrences where value in Col A is 0
(implying a date) and if TRUE then second gives the count of 0's..

"force530" wrote:

Sorry, I cant get either to work. It must be my explanation.

In Column A, record "960" appears 3 times. In column B, there is a date and
2 zeros next to record 960 on different rows. Since there is a date next to
at least one of these records, I want to count the number of zeros, in this
case it would be "2".

Now, there are more than 26000 entries in this file. I want to count the
number of zeros based on the above condition for each unique record. It maybe
a sort or pivot table issue. I dont know, I have tried both.

"Teethless mama" wrote:

Simplify version:

=SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0))


"Sheeloo" wrote:

Put your unique nos in Col C
then enter this in D1 and copy down
=IF(SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$1000))0,SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100=0)),0)

Adjust 100 to the end of your data set.

"force530" wrote:

Column A contained a unique identifier for each record. I want to be able to
count the number of records with 0's, if a date is also recorded for the same
unique number. Ex: A1, A2, A3 is record 960. A1 has a date, A2 and A3 are
0's. The answer I am looking for would be "2". A8 and A9 is 522. The answer
would be 1.

In the end the final number I am looking for is the sum of 2 and 1, "3".

A B
1 960 7/22/2004
2 960 0
3 960 0
4 967 0
5 990 8/7/2008
6 12 2/6/2007
7 43 10/16/2008
8 522 8/29/2008
9 522 0
10 174 2/28/2007