Countif worksheet function frustrations

S

Santa-D

I've got a worksheet that uses the following countif function:

{=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.cost.1))}

and it works, B97 is a currency value.

In another worksheet I have

{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),mhc.lease.no))}

Where F17 is a text field, but the second one doesn't work.

However, this works instead

{=SUM(IF((mhc.lease.no=F17)*(mhc.dept="DCS"),1,0))}

Is there any reason why the first countif works but not the second?
 
C

Carim

Hi,

Have you tried :
{=COUNT(IF(--(mhc.lease.no=F17)*(mhc.dept="DOTAG"),mhc.lease.no))}


HTH
Carim
 
B

Biff

Is there any reason why the first countif works but not the second?
{=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.cost.1))}
and it works, B97 is a currency value.
Click to expand...

It works because bay.cost.1 are numeric values and that's what COUNT does.
It counts numbers.
{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),mhc.lease.no))}
Click to expand...

Your other formula would like this:

{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),1))}

However, there's a better way to do this using non-array formulas:

=SUMPRODUCT(--(bay.cost.1=B97),--(dept.1="DOTAG"))

=SUMPRODUCT(--(mhc.lease.no=F17),--(mhc.dept="DOTAG"))

Biff
 
B

Biff

Ooops! Missing word: (how do we miss entire words?)
Your other formula would like this:
{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),1))}
Click to expand...

Should be:

Your other formula would work like this:

Biff
 
T

Teethless mama

{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),mhc.lease.no))}

For this model, you have to use COUNTA instead of COUNT
 
H

Harlan Grove

Biff wrote...
....
Your other formula would like this:

{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),1))}
Click to expand...
....

Or it could look like

=COUNT(1/((mhc.lease.no=F17)*(mhc.dept="DOTAG")))

but agree that SUMPRODUCT would be better.
 
S

Santa-D

=SUMPRODUCT(--(mhc.lease.no=F17),--(mhc.dept="DOTAG"))

What does the -- represent?
I've been looking in a few ebooks that I have and i'm unable to locate
any reference to the "--"
 
Top