want to see if a range doesn't contains any values other than this

[banneduser123]

hi,

can't figure this out.

I want to create a formula that will check all of column A, to see if it contains any values other than "October".

So here's an example:

Column A
October
October
March
October
April


In the example above, the formula should return ether "Yes" or "No". That doesn't matter, i just don't know how to write a formula that says if this range contains any values other than "October", then tell me "Yes".


hope that was clear..

 

[mikerickson]

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

or if you want a blank cell to trigger the Yes response, hard code in the 10.

 

[banneduser123]

hey Mike, thanks for the response.

tried applying this formula, but not really sure how it's working....doesn't seem to accomplish what i was looking for.
can you explain what your formula does?

 

[Peter_SSs]

The formula was missing a quote mark. Try the one below (I've give 2 examples) . If it is not what you want, be more specific about what went wrong and perhaps give your (small) sample data and the expected results.

Excel Workbook
	A	B	C	D	E
1	October	Yes		October	No
2	October			October
3	March			October
4	October			October
5	April			October
6				October
7				October
8				October
9				October
10				October
Same Values

 

[banneduser123]

Hi Peter,

thanks for helping.

i tried the formula in a new workbook and it worked fine, it's not working in the file i actually need it in...not sure why. at first i thought it was cause i wasn't factoring in the header of the column, but i dont know.


also not sure how you entered tables in your post...


so i have two files.

File 1 has a column of values that I want to look through.
File two is where I have the formula i want to create.

File 1, column B:

B1: Term
B2: 1 Year
B3: 1 Year
B4: 1 Year
B5: 1 Year



File 2:

A1: formula will look in column B of File 1, is there are any values others than "1 Year" and "Term", then return "Yes"



was this clear?

 

[Peter_SSs]

so i have two files.

Do you actually mean 2 files, or do you mean 2 worksheets in a workbook?

formula will look in column B of File 1,

How much of column B does it need to look in? Surely not all 1,000,000+rows?

 

[banneduser123]

i mean two files.

only needs to look at maybe 2000 rows

 

[banneduser123]

when i click on evaluate formula, it shows me the countif is coming out to 756, but the counta shows as 757..the counta always comes up with one count more no matter how much i toy around with this...

'=IF(COUNTIF('[Working Paper 083119.xlsx]Raw'!$G:$G,"GT 1 YEAR")*COUNTA('[Working Paper 083119.xlsx]Raw'!$G:$G),"no","yes")
same with
'=IF(COUNTIF('[Working Paper 083119.xlsx]Raw'!$G$2:$G$2000,"GT 1 YEAR")*COUNTA('[Working Paper 083119.xlsx]Raw'!$G$2:$G$2000),"no","yes")


also, even if i change the formula to be this, it still always returns the value as true

'=IF(COUNTIF('[Working Paper 083119.xlsx]Raw'!$G$2:$G$2000,"GT 1 YEAR")*COUNTA('[Working Paper 083119.xlsx]Raw'!$G$3:$G$2000),"no","yes")

 

[Rick Rothstein]

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

Spreadsheet Formulas

Cell Formula B1 =IF(COUNTIF(A1:A10,"October")=COUNTA(A1:A10), "No", "Yes") <tbody> </tbody>

<tbody>
</tbody>

Am I missing something... why not use the COUNTIFS function?

=IF(COUNTIFS(A1:A10,"<>October",A1:A10,"<>"),"Yes","No")

 

[banneduser123]

looks like it's working properly with this:


=IF(COUNTIF('[Working Paper 083119.xlsx]Raw'!$G2:$G2222,"GT 1 YEAR")=COUNTA('[Working Paper 083119.xlsx]Raw'!$G2:$G2222),"no","yes")


not really sure where i was making my mistake, but thanks for the help everyone!