Readin text in an if staement

Marv031261
Marv031261 used Ask the Experts™
on
I  have a nested If statement that must do a countif if two criterion are met. The first criteria iis that  a certain name must be found under the site name column, the second criteria is there must be some text in the  error column section. If both comments are met then a count  is to be done for each  piece of data that meets both criteria.  the statement follows
IF(AND(D6:D18="cheyenne",F6:F18<>""),COUNTIF(A6:F18,"b"),"fail") the is a spreadsheet also attached

Evauation id      reviewer      Tape-id      site name      capture date      errors
1      m      15      Bryan      3/12/2011      yes
1      m      15      Bryan      3/13/2011      yes
1      m      15      Bryan      3/14/2011      
1      m      15      Houston      3/15/2011      yes
1      m      15      Houston      3/16/2011      yes
1      m      15      Houston      3/17/2011      no
1      m      15      Tulsa      3/18/2011      no
1      m      15      Tulsa      3/19/2011      no
1      m      15      Tulsa      3/20/2011      no
1      m      15      Cheyenne      3/21/2011      
1      m      15      Cheyenne      3/22/2011      no
1      m      15      Cheyenne      3/23/2011      no
1      m      15      Cheyenne      3/24/2011      no



URS.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
You can use the following formula in cell H6 and copy to the right
=COUNTIFS($D$6:$D$18,H$5,$F$6:$F$18,"?*")

Do a custom format as follows if you want the 0 values to show as "Fail"
0;;"Fail"

Or use the extended formula
=if(0=COUNTIFS($D$6:$D$18,H$5,$F$6:$F$18,"?*"),COUNTIFS($D$6:$D$18,H$5,$F$6:$F$18,"?*"),"Fail")

Thomas

Author

Commented:
Thom ,  can u do the exmple on the attached spreadsheet  and show me the fomula where a count is attach it?  The criteria is that the ashed for name must be in the site name column and there must be text in the ERRor column. if both  criteria are met  then do a countif on the site name in the first part of the  if statement?
Top Expert 2008
Commented:
Here is what I have:


URS.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial