[mso] Re: IF formulas in Excel

Not sure where you want the result, but your formula, 
(COUNT(IF,B:B="BCR",1,0)*AND(COUNT(IF(C:C="C-DU",1,0))) doesn't look correct to 
me. 
Changing it to =(IF(B:B="BCR",1,0)* (IF(C:C="C-DU",1,0))) will put a '1' in the 
column with this formula when both conditions are met, and a 0 if both are not 
met. Then you can sum the column. I'm sure Tom's formula will work if you have 
Excel 2007, as he's pretty good :) 





----- Original Message ----- 
From: "Thomas Hutchins" <hutch99999@xxxxxxxxx> 
To: mso@xxxxxxxxxxxxx 
Sent: Friday, March 27, 2009 1:44:08 PM GMT -06:00 US/Canada Central 
Subject: [mso] Re: IF formulas in Excel 

You can do that easily with SUMPRODUCT: 
� 
=SUMPRODUCT(--(B1:B10000="BCR"),--(C1:C10000="C-DU")) 
� 
Please note that, unless you are using Excel 2007, you can't refer to whole 
columns using SUMPRODUCT. 
Hope this helps, 
� 
Hutch 

--- On Fri, 3/27/09, John Perrin <jperrin@xxxxxxxxxxxxx> wrote: 


From: John Perrin <jperrin@xxxxxxxxxxxxx> 
Subject: [mso] IF formulas in Excel 
To: mso@xxxxxxxxxxxxx 
Date: Friday, March 27, 2009, 1:17 PM 


-----Inline Attachment Follows----- 


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




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

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