How to get a cell to return "Yes" if all values in a column equal "X"

[BRIEBRIE123]

Hi there,

Piggybacking off a previous thread I found in this forum, I used the following formula to return "Yes" if all cells in my column were either blank, or were the same:

=IF(COUNTIFS(C14:C40,"<>"&"",C14:C40,"<>"&"C14"),"No","Yes")

I am trying to use this formula for another similar second column, however I can't get it to work properly (it does not return "Yes" if all values are the same". The only difference between the two columns is that the first one i used it for included 1) cells with values and 2) blank cells. The second, however, includes both 1) columns with values (formula-driven) and 2) columns that are blank as a result of an =IFERROR(...,"") formula which results in a "blank cell".

I was just wondering if there was a reason as to why the =IF(COUNTIFS() formula is not working with the second formula-driven column? I assumed it would work, as I included an IFERROR statement that transforms #N/As into blank cells? I can't seem to figure out why!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")

 

[DanteAmor]

=IF(COUNTIFS(C14:C40,"<>"&"",C14:C40,"<>"&"C14"),"No","Yes")

If you put quotes in "C14" it means that you literally compare against the C14 text.
To compare against cell C14 it must be without quotes:

=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")

 

[BRIEBRIE123]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")

Hi and thank you! I tried using this formula however it seems I'm still running into the same issue (still returns "No" even though all values are either the same or return a blank using my IFERROR statement)?

To provide some additional information, please see below the column in question. As you can see here, there are 3 cells that have been filled out using the VLOOKUP formula. The remainder of cells (column range is W4-W999) below are blank ("") as the VLOOKUP formula did not return a result. In a case like this, I would like the IF(COUNTIFS) formula to return a "Yes".

have modified the formula provided to fit my worksheet:

=IF(COUNTIFS(W4:W999,"<>",W4:W999,"<>"&W4),"No","Yes").

 

[BRIEBRIE123]

=IF(COUNTIFS(C14:C40,"<>"&"",C14:C40,"<>"&"C14"),"No","Yes")

If you put quotes in "C14" it means that you literally compare against the C14 text.
To compare against cell C14 it must be without quotes:

=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")

Hi there, Thanks for the clarification!

I tried using this formula however it seems I'm still running into the same issue (still returns "No" even though all values are either the same or return a blank using my IFERROR statement)?

To provide some additional information, please see below the column in question. As you can see here, there are 3 cells that have been filled out using the VLOOKUP formula. The remainder of cells (column range is W4-W999) below are blank ("") as the VLOOKUP formula did not return a result. In a case like this, I would like the IF(COUNTIFS) formula to return a "Yes".

have modified the formula provided to fit my worksheet:

=IF(COUNTIFS(W4:W999,"<>",W4:W999,"<>"&W4),"No","Yes").

 

[Fluff]

Ok, how about

=IF(COUNTIFS(W4:W999,"?*",W4:W999,"<>"&W4),"No","Yes")

 

[BRIEBRIE123]

Ok, how about

=IF(COUNTIFS(W4:W999,"?*",W4:W999,"<>"&W4),"No","Yes")

It works, thank you so much!! Much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.