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

#### BRIEBRIE123

##### New Member
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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")``

#### DanteAmor

##### Well-known Member
=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

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=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

##### New Member
=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

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(COUNTIFS(W4:W999,"?*",W4:W999,"<>"&W4),"No","Yes")``

#### BRIEBRIE123

##### New Member
Excel Formula:
``=IF(COUNTIFS(W4:W999,"?*",W4:W999,"<>"&W4),"No","Yes")``
It works, thank you so much!! Much appreciated

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
4
Views
133
Replies
4
Views
76
Replies
1
Views
142
Replies
3
Views
54
Replies
2
Views
69

1,191,119
Messages
5,984,758
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back