Excel-How to make "yes" and "NA" and be counted as a "Yes"
I am trying to write a function that analyzes a column for "yes", "NA", and "no". If there is a "yes" or a "NA" in all the rows then the Submittal status would be "yes" (there could be all yes's and 1 NA and the Submittal Status would have to be "yes"). However, if there is a "No" in any row the the Submittal Status would have to be "No" This is what I have:
=IF(COUNTIFS(H4:H14,{"Yes","NA"})=COUNTA(H4:H14),"Yes","No")
It is only giving me a "yes" when all rows are "yes". When a row appears as "NA" with all other rows as "Yes" it displays "No" in the Submittal Status area which is wrong. All "No"s in rows display a Submittal Status of "No" which is correct. Can someone help me figure out this code?
21 Answer
I see three ways to make this work.
Per cybernetic.nomad's comment, if you focus on the problem statement of "...if there is a "No" in any row...", we want to count cells with "No" in them.
=IF(COUNTIF(H4:H14,"No")=0,"Yes","No")
If on the other hand, you want to focus on it from the other direction -- "...If there is a "yes" or a "NA" in all the rows...", then we want to count the cells with a "yes" and add that to the count of cells with "NA".
=IF(COUNTIFS(H4:H14,"Yes")+COUNTIFS(H4:H14,"NA")=COUNTA(H4:H14),"Yes","No")
And, if we want to use the {...} syntax, as you had in your initial attempt, we just have to use a SUM(...) construct, because the COUNTIFS function with the {"Yes","NA"} approach returns an array of two values (one for each possible match from the list), so we need to add them up.
=IF(SUM(COUNTIFS(H4:H14,{"Yes","NA"}))=COUNTA(H4:H14),"Yes","No")
(The third approach is just a restatement of the second approach.)
Caution
The first two approaches differ in what could be a significant way. To decide which is best for you, you'll have to decide what you want the result to be if there are any values outside your examples of "Yes", No", and "NA". In particular, if any row has a non-listed value, (say, "xxx"), the first approach will result in a Yes, while the second and third approaches will result in a No.
Your requirements are ambiguous, and while this is not likely to be a problem for you in this case, it is a good practice to not leave anything to chance.
