[mso] IF formulas in Excel

After reading some of the posts in your archive section I think this might seem 
like an elementary question to college students but I'll ask it anyway because 
a professor used to tell me the only truly stupid question is one that you need 
an answer to but chose not to ask.  Or at least something along those lines.
I would like to create a template for the labs that I oversee for data 
collection and management where the supervisor could put the template on their 
desktop, dump the required data into the appropriate columns and the formulas 
off to the right would give them the needed numbers for that month.
I have all of the COUNTIF formulas working fine so I can see volume of testing. 
 What I can't seem to tease out of a formula is a calculation that looks at 
column B for a specific test, then at column C for a specific result and count 
only those that meet both criteria I have set.  I thought I had the formula 
down today but doesn't quite seem to work.  Just a little background (BCR is 
test code in our Molecular Lab for genes involving Breast Cancer and C-DU is a 
test canceled because it is a duplicate specimen).  Here is the formula I have 
so far:
=(COUNT(IF,B:B="BCR",1,0)*AND(COUNT(IF(C:C="C-DU",1,0)))

Column B contains the test code for each test run and column C has all of the 
test results or whether the test was canceled.  In column C there also results 
of "POS", "NEG" or "INC" for inconclusive.  Each time I change the "C-DU" to 
"POS" or "NEG" the number from the formula is always 3.  Just to make sure I 
have verified all of the data in the worksheet.  In February 2009, our lab 
received 91 specimens for BCR testing, 40 were NEG, 6 were INC, 2 C-DU, 1 C-OE 
(order error) and 42 were POS.

John Perrin
jperrin@xxxxxxxxxxxxx 

**********************************************************
Electronic Mail is not secure, may not be read every day, and should not be 
used for urgent or sensitive issues
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: