[mso] Re: IF formulas in Excel

I'm kinda strapped for time right now, John, but at a quick glance I think
you need an array formula ... here's my quick tutorial on arrays ... see if
this is what you are looking for:

http://personal-computer-tutor.com/arrayformulas.htm


Linda

*********************************
Linda F. Johnson
Linda's Computer Stop
http://personal-computer-tutor.com


-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of John Perrin
Sent: Friday, March 27, 2009 2:18 PM
To: mso@xxxxxxxxxxxxx
Subject: [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 

*************************************************************
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: