=QUERY(A1:C,"Select A,B,C WHERE C !=date '"&TEXT(E1,"yyyy-mm-dd")&"' and A is not null") The formula to use when the criterion is in cell E1. Here is the != comparison operator in date column filtering. You can use the long-winded approach for this conversion.īack to our topic, not equal to in Query in Google Sheets. So the date criterion should be converted to a string to use in Query. Even if you use, sometimes the formula may return an incorrect result. The reason you can’t use the date as it’s in Query.
Why the date criterion in Query is confusing? You can check that when you have time – date in Query as the filter criterion. Keep that in mind, I have written a detailed tutorial separately on the use of the date in Query as the criterion. I guess this is the case with many Google Sheets users out there. I always find the use of the date criterion in Query quite confusing. =QUERY(A1:C,"Select A,B,C WHERE B !=1 and A is not null") Formula # 3 (!= in date column) This one when the criterion is within the formula. =QUERY(A1:C,"Select A,B,C WHERE B !="&E1&" and A is not null") Use this Query formula when the criterion is in cell E1.
Here let me show you how to filter column B if the values in column B is not equal to #1. =QUERY(A1:C,"Select A,B,C WHERE A !='AB10025YX 2' and A is not null") Formula # 2 (!= in Number/Numeric column) In the following formula, the criterion is within the formula which will also return the above output. Refer this image to see the criterion in cell E1 and the formula in cell E3. =QUERY(A1:C,"Select A,B,C WHERE A !='"&E1&"' and A is not null") This value is called criteria which I have in the cell E1. I am filtering column A that doesn’t match the value “AB10025YX 2”. How to Use != Not Equal to Operator in Query Here are the formulas containing the different operators. Let’s see how to use not equal to in Query in Google Sheets. The first column in this sample data contains text strings, the second column contains numbers and the third column contains dates. We can test the operators in these three columns individually. The data contains one each text, numeric and date columns. I have the following demo data which is well formatted for testing the above said different not equal to comparison operators in Query. Other than these two comparison operators there is one complex string comparison operator that is none other than the Matches regular expression match. The use of not equal to in Query depends on the content of the column. The usage will be different if the column contents are text, numbers or dates.Īs I have mentioned above, there are two simple comparison operators that you can use to get the not equal to in Query in Google Sheets. There are two simple and one complex comparison operators to get the not equal to in Query in Google Sheets. You can use any of them to filter columns.