Biff/Max

Seems to be a problem using the formula C2 =IF(AND(A1<A2,B20),1,"") for
some other rows. Say, what if the date for NC-01 is at B4 (rather than B2
previously), it doesn't give 1.

Code Date Count
NC-01
NC-01
NC-01 12/05/05
NC-02 10/08/05 1
NC-02 12/08/05
NC-03
NC-03
NC-04 01/05/05 1
NC-05 30/05/05 1

However, the longer formula
(=SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10, 0)),MATCH(A2:A10,A2:A10,0))0)) works fine.

Carlos





"Carlos" wrote:

Thank you guys for quick reply.

Max's formula give me 5 counts.

Biff's two formulas give me the correct answer I want. I never thought of
using AND function. . great!

Thanks again.

Carlos




"Biff" wrote:

Hi!

If you're using this as a helper formula and your true goal is to count
unique codes that have a date:

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF(B2:B100,MATCH(A2:A10,A2:A10,0 )),MATCH(A2:A10,A2:A10,0))0))

If that's not what you're trying to do:

Enter this formula in C2 and copy down as needed:

=IF(AND(A1<A2,B20),1,"")

Note: A1 in the above formula is not a typo.

Biff

"Carlos" wrote in message
...
Hi,

I can't figure out the correct formula.

Code Date Wrong Correct
NC-01 12/05/05 0 1
NC-01 0
NC-01 0
NC-02 10/08/05 0 1
NC-02 12/08/05 1
NC-03 0
NC-03 0
NC-04 01/05/05 1 1
NC-05 30/05/05 1 1

The idea is, if the code has a date then it will give a count 1, otherwise
0. My formula in column C (Wrong column) is =IF(A2=A3,0,IF(B2="",0,1)). In
this case, I should have a 4 Codes with dates. What sort of trick to use
to
give me the correct count? Please advise. Thanks in advance.

Carlos