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.
Google-sheets – Use of Countifs to check values from two different columns to match a criteria
google sheets
Related Topic
- Google-sheets – Combing two ranges with identical column structure from two different sheets (in same spreadsheet)
- Google-sheets – Finding the number of instructors available on different weekends in Google Sheets
- Google-sheets – How to compare values in two columns
- Google Sheets – Fix Date and Time Formatting with Google Apps Script
- Google-sheets – Trying to search(from sheet1 ) for a value on in any row of sheet2
- Google Sheets COUNTIFS with INDEX and OR Criteria
Best Answer
You have specifically referenced
COUNTIFS()
for the answer to your question. Please consider that there are likely to be several other alternatives (includingfilter
andquery
) that will also produce the same result.Establishing criteria
In your analysis, you defined an error as any of the following:
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")