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

BRIEBRIE123

New Member
Joined
Dec 30, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(COUNTIFS(C14:C40,"<>",C14:C40,"<>"&C14),"No","Yes")
 
Upvote 0
=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")
 
Upvote 0
Hi & welcome to MrExcel.
How about
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".

1640889076974.png


have modified the formula provided to fit my worksheet:

=IF(COUNTIFS(W4:W999,"<>",W4:W999,"<>"&W4),"No","Yes").
 
Upvote 0
=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".

1640889076974.png



have modified the formula provided to fit my worksheet:

=IF(COUNTIFS(W4:W999,"<>",W4:W999,"<>"&W4),"No","Yes").
 
Upvote 0
Ok, how about
Excel Formula:
=IF(COUNTIFS(W4:W999,"?*",W4:W999,"<>"&W4),"No","Yes")
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top