1

I have a data with 3 tabs. Tab 1 has information to be analyzed in tab 3. So in tab 1 I have column J and K which have responses of either "yes" or "No" in one row. So if for example row 5 column J shows "Yes" while K shows No/ J"No" and K"Yes"/both J and K shows No, I term that as an error. If both J and K are Yes then I need not to count that. Which formular can I use.

0

You have specifically referenced COUNTIFS() for the answer to your question. Please consider that there are likely to be several other alternatives (including filter and query) that will also produce the same result.

Establishing criteria

In your analysis, you defined an error as any of the following:

  • J = "Yes" and K = "No"
  • J = "No" and K = "Yes"
  • J and K shows "No"

As shown below, "J and K shows No" is redundant because it is covered under the criterion of "J = "No".

Formula

=COUNTIFS(J4:J23,"<>Yes")+COUNTIFS(J4:J23,"Yes",K4:K23,"No")


Sample data


Formulae


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.