Readin text in an if staement

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
Microsoft Excel

Last Comment
nutsch
nutsch

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
Marv031261

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?
nutsch

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY