CountIF
 

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

One way to try ..
Put in C2: = --AND(ISNUMBER(B2),B2<0)
Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"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

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

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

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

Max's formula give me 5 counts.

First, my apologies for missing the significance of your line:
... I should have a 4 Codes with dates ...
which inferred that the counts were to be for unique codes

A non-array alternative to try would be to put:

In C2: =IF(AND(ISNUMBER(B2),B2<0),A2,"")
In D2: = --(AND(C2<"",COUNTIF($C$2:C2,C2)=1))

Then select C2:D2 and fill down

Col D will return the count results that you seek

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max,

Thanks, thanks.... and thanks. That's exactly what I need. Just wonder what
does the two dashes "--" means in D2 formula?

Rgrds
Carlos

"Max" wrote:

Max's formula give me 5 counts.

First, my apologies for missing the significance of your line:
... I should have a 4 Codes with dates ...
which inferred that the counts were to be for unique codes

A non-array alternative to try would be to put:

In C2: =IF(AND(ISNUMBER(B2),B2<0),A2,"")
In D2: = --(AND(C2<"",COUNTIF($C$2:C2,C2)=1))

Then select C2:D2 and fill down

Col D will return the count results that you seek

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad to hear it's of some help <g !

.. what does the two dashes "--" mean in D2 formula?

The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Carlos" wrote in message
...
Max,

Thanks, thanks.... and thanks. That's exactly what I need. Just wonder
what
does the two dashes "--" means in D2 formula?

Rgrds
Carlos

Mmmm..... i guess i don't need to know it further and seems i have a lot more
things to know about Excel. Thanks again.

God bless.

Carlos

"Max" wrote:

Glad to hear it's of some help <g !

.. what does the two dashes "--" mean in D2 formula?

The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Carlos" wrote in message
...
Max,

Thanks, thanks.... and thanks. That's exactly what I need. Just wonder
what
does the two dashes "--" means in D2 formula?

Rgrds
Carlos

You're welcome, Carlos !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----