CountIF

[Guest]

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]

One way to try ..
Put in C2: = --AND(ISNUMBER(B2),B2<>0)
Copy down

 

[Biff]

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:B10>0,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,B2>0),1,"")

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

Biff

 

[Guest]

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

 

[Guest]

Biff/Max

Seems to be a problem using the formula C2 =IF(AND(A1<>A2,B2>0),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:B10>0,MATCH(A2:A10,A2:A10,0)),MATCH(A2:A10,A2:A10,0))>0)) works fine.

Carlos

 

[Max]

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 C22 and fill down

Col D will return the count results that you seek

 

[Guest]

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]

.. 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

 

[Guest]

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]

You're welcome, Carlos !
Thanks for the feedback ..