Help with IF AND?

[sammipd]

I need to answer the question " How many who were referred (Yes) had at least one test (x)" on a tab separate from the data tab.
I'm working with a data tab (1000+ lines) and a reporting tab. Using =IF(AND(A2="yes",B2:E2<>""),"Referred to Test","") on the data tab will tell me which lines meet the criteria, however, using that formula from the reporting tab pointing to the data tab results in a #Value! error. And it doesn't answer my question. How can I get a total number of all 1000+ lines of data who were referred with at least one test? Can you help? Thanks!

 

[john316swan]

You can use a helper column in f with the formula: =IF(A2="yes",COUNTIF(B2:E2,"x"),0)

Then you can sum(F:F) in another cell

 

[john316swan]

Oops, you want number of people who were yes with at least 1, use this formula instead: =IF(A2="yes",COUNTIF(B2:E2,"x")>0)

 

[sammipd]

Thank you john316swan