using a row filled with strings to determine cell value

[NebJoel]

I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".

 

[Sheeloo]

If yes and no are in Col A
then put this in B1
=IF(COUNTA(A:A)=COUNTIF(A:A,"yes"),"Yes","No")

if you know the last cell in Col A (say A100) then you can replace A:A by
A1:A100 in the formula and put the formula in A101

 

[Gary''s Student]

Try the following:

Sub makechange()
Set r = Range("A1:A100")
If Application.WorksheetFunction.CountIf(r, "Yes") = 100 Then
Exit Sub
Range("A100").Value = "No"
End Sub

If the column is A1 thru A100 and all the values are "Yes", then A100 is
already "Yes" and no change is required. If any value is not "Yes" then A100
is set to "No"

Adjust the range to suit your needs.

 

[Bernie Deitrick]

=IF(COUNTIF(A2:A10,"Yes")=COUNTA(A1:A10),"Yes","No")


HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

This assumes NO header row. If a header row change to =lr-1

Sub ifallyes()
mc = "i"
lr = Cells(Rows.Count, mc).End(xlUp).Row
If Application.CountIf(Columns(mc), "yes") = _
lr Then Cells(lr, mc) = "No"
End Sub

 

[Alojz]

How would u like this one?

=if(sum((A1:A11="yes")^2)-row(A11)+row(A1);"yes";"no")

press ctrl+shift+enter after u insert in calculation cell

if anything else than "yes" is in A1:A11, my formula returns "no"