Countif worksheet function frustrations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Santa-D - 25 Oct 2006 06:32 GMT

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?

Reply to this Message

Carim - 25 Oct 2006 06:40 GMT

Hi,

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

HTH
Carim

Reply to this Message

Biff - 25 Oct 2006 06:50 GMT

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

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))}

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

> I've got a worksheet that uses the following countif function:
>
[quoted text clipped - 13 lines]
>
> Is there any reason why the first countif works but not the second?

Reply to this Message

Biff - 25 Oct 2006 07:03 GMT

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))}

Should be:

Your other formula would work like this:

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))}
[quoted text clipped - 34 lines]
>>
>> Is there any reason why the first countif works but not the second?

Reply to this Message

Harlan Grove - 25 Oct 2006 19:44 GMT

Biff wrote...
...

>Your other formula would like this:
>
>{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),1))}

...

Or it could look like

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

but agree that SUMPRODUCT would be better.

Reply to this Message

Santa-D - 30 Oct 2006 05:12 GMT

=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 "--"

Reply to this Message

Ken Puls - 30 Oct 2006 06:14 GMT

It coerces a True/False result into a 1/0.

Best place to read up on this is Bob Phillips site. He gets into this
just over half way through this article:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

> =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 "--"

Reply to this Message

Teethless mama - 25 Oct 2006 15:47 GMT

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

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

> I've got a worksheet that uses the following countif function:
>
[quoted text clipped - 13 lines]
>
> Is there any reason why the first countif works but not the second?

Reply to this Message

Santa-D - 30 Oct 2006 04:58 GMT

Hi everyone, thank you for your help & support. It's been fantastic.

Reply to this Message