Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
I've got a worksheet that uses the following countif function:
{=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.c ost.1))} and it works, B97 is a currency value. In another worksheet I have {=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),m hc.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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
Hi,
Have you tried : {=COUNT(IF(--(mhc.lease.no=F17)*(mhc.dept="DOTAG"),mhc.lease.no ))} HTH Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
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 "Santa-D" wrote in message ups.com... I've got a worksheet that uses the following countif function: {=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.c ost.1))} and it works, B97 is a currency value. In another worksheet I have {=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),m hc.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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
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 "Biff" wrote in message ... 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 "Santa-D" wrote in message ups.com... I've got a worksheet that uses the following countif function: {=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.c ost.1))} and it works, B97 is a currency value. In another worksheet I have {=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),m hc.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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
{=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),m hc.lease.no))}
For this model, you have to use COUNTA instead of COUNT "Santa-D" wrote: I've got a worksheet that uses the following countif function: {=COUNT(IF((bay.cost.1=B97)*(dept.1="DOTAG"),bay.c ost.1))} and it works, B97 is a currency value. In another worksheet I have {=COUNT(IF((mhc.lease.no=F17)*(mhc.dept="DOTAG"),m hc.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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
Hi everyone, thank you for your help & support. It's been fantastic.
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
=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 "--" |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif worksheet function frustrations
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 Santa-D wrote: =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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
CountIf Function Question | Excel Discussion (Misc queries) | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
CountIf Function Help Needed | Excel Worksheet Functions | |||
changing type style and colour within a worksheet function | Excel Worksheet Functions |