HomeGoogle DocsSpreadsheetHow to Use IF Function in Google Sheets Query Formula

How to Use IF Function in Google Sheets Query Formula

Published on

Without telling you the purpose, from my below tutorial, you can’t learn the use of the IF Function in Google Sheets Query.

First of all, let me prepare some sample data to experiment with the IF statement in Query. After that, I’ll explain the purpose.

Sample Data for IF Logical Formula Test in Query

On your Google Sheets, in Cell A1, apply the below formula to get the required sample data to learn the use of the IF Statement in Query Where Clause.

=query(importhtml("https://en.wikipedia.org/wiki/List_of_best-selling_fiction_authors","Table",1),"Select Col1,Col7")

I’m not going to explain the above IMPORTHTML based formula. If you are not familiar with using this formula, please check my Google Sheets Function Guide, pick IMPORTHTML from there.

The above formula, without the Query, returns a seven-column table from a Wikipedia Page.

With the help of the Query, I’ve excluded columns 2 to 6.

Here is the partial screenshot of the above imported, Queried data.

Sample Data for IF statement in Query Formula Example

With this sample data, the list of best-selling authors in the fiction category, you can learn how to use the IF function in Google Sheets Query.

The Purpose of IF Function in Google Sheets Query

I hope, by now, you have imported the above table into your Google Spreadsheet.

If, for any reason, you can’t import the data, refer to the above screenshot and prepare the data based on it. That will be enough.

Now to the purpose of IF in Query Formula

Usually, the IF logical test is used in Query to bring dynamism to the filter.

To understand this, first, prepare a drop-down menu in Cell D2.

You can follow the below steps to create a drop-down menu in cell D2.

Steps to Create a Drop-Down Menu for Query Filter

1. Go to cell D2.

2. Then go to the menu Data > Data Validation.

3. In Data Validation, choose “List of items” as Criteria and enter the following text strings in the field provided – “British,” “English,” “American,” and “All.”

Please enter the text strings as shown in the screenshot below.

drop down menu for Query If Test

4. Click “Save.” Your drop-down menu is ready in cell D2.

Now I want to filter the above two-column data based on the condition in cell D2.

Query Criterion in Drop Down Menu

In our sample data, please check the column headings in cells A1 and B1. From that, you can understand one thing.

Column 1 contains the name of the best-selling authors in the Fiction category, and column 2 is their citizenship details.

We are going to filter this data based on the author’s citizenship/nationality in column 2.

The criterion is already ready in column D as a drop-down.

The following formula can filter the data based on the condition in D2.

=query(A1:B,"Select A where B='"&D2&"'",1)

You May Like: Different Error Types in Google Sheets and How to Correct It.

If you select “British” in cell D2, the formula will return all the authors whose citizenship is British.

IF logical test in Query in Action

But what about the Criteria “All” in D2? Since it’s not citizenship, there is no such string in column B.

What I want to do by selecting this criterion from the drop-down menu is, list all the authors irrespective of their citizenship.

Here we can use the IF Function in Google Sheets Query.

In other words, the purpose of the IF statement in Query is to bring dynamism to the filter clause.

How to Use the IF Logical Function in Google Sheets Query

Honestly, the usage of single and double quotes in Query is quite confusing, even for an advanced user.

Sometimes, I also fall into difficulties due to the wrong placement of Quotes in Query.

So do give more attention to the usage.

The formula that Explains How to Use the IF Function in the Where Clause in Query Function

Formula:

=query(A1:B,"Select A " & IF(D2="All",, "where B = '"&D2&"' "),1)

Refer to the below screenshot.

If statement in conditional filter in Query

If Statement in Query – Explanation

If the value in Cell D2 is “All”, the formula would function as below. The strikethrough portion is not required.

=query(A1:B,"Select A " & IF(D2="All",, "where B = '"&D2&"' "),1)

Because the IF function returns null and to understand this, select “All” in cell D2.

In any cell, enter the IF logical formula alone, and you can see that it returns blank.

Change the value in cell D2 to “American.” The IF formula output will be as below.

details of the usage of If in Query Where Clause

In this case, the Query formula would read the IF statement as below.

=query(A1:B,"Select A where B = 'American'",1)

That’s all about how to use the IF Function in Google Sheets Query.

Conclusion

In Query, as said many times, you should pay extra attention to the use of Quotes.

Otherwise, you may end up seeing the #VALUE error on your sheet. That’s all. Enjoy.

Related: Google Sheets: How to Get an All Selection Option in a Drop-down.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

23 COMMENTS

  1. Hi Prashanth,

    I have an issue with an IFS function working in cell C4 of the undermentioned sheet.

    The function is to confirm if cells A4:A259 are less than the average in cell A260 and if cells in B4:B259 are greater than the average in B260.

    And in cells C4:C259, it should display 2 if A and B are true, 1 if only one of A or B is true, or blank if neither A nor B is true.

    Sample Sheet: URL removed by admin

  2. THANK YOU SO MUCH, PRASHANTH!

    Just for clarification, I found (after reviewing your Purpose of WHERE 1=1 blog) that I needed to add the group by A in the first "" also to make it work — so:

    =QUERY(A2:C,"SELECT A, count(B) WHERE 1=1 "&IF(D2="All"," group by A"," AND B = '"&D2&"' group by A"))

    This is brilliant! Thank you!

  3. Hi, Prashanth! This is really helpful – thank you.

    How do you include aggregations in this formula?

    If I add the “group by” before the IF statement, I can get results as long as “ALL” is selected; but if I make a selection (in this example, choose British), then I get a parse error.

    example: query(A1:C,"Select A, count(B) group by A " & IF(D2="All",, “where B = '"&D2&"' "))

    I know the where clause should precede the Group By, but I couldn’t get that to work at all here.

  4. Hi, I’m looking at multiple data range sources for my Query.

    Sometimes the other range would not return any result at all, which causes the Query to have an error.

    =QUERY({Row_Janilla;Row_Nikita},"Select * where B = 66",0)

    The range Row_Janilla on its own returns values, but the range Row_Nikita does not.

    I’d like for the Query to still allow to look into multiple ranges from different tabs and ignore the data range if it has no result.

    Can anyone help?

        • Hi, Yepz,

          I could understand that you want to use multiple IF logical tests in the Query WHERE clause.

          The criteria are in cells F7, F8, and F10 as drop-downs.

          1. If F7= “ALL AREAS”, ignore it, else consider it.
          2. If F8= “ALL CLUSTER”, ignore it, else consider it.
          3. If F10= “ALL PERSONNEL”, ignore it, else consider it.

          The below part does that.

          "WHERE"&if(F7="ALL AREAS",," lower(E)=lower('"&F7&"') and")&
          if(F8="ALL CLUSTER",," lower(F)=lower('"&F8&"') and")&
          if(F10="ALL PERSONNEL",," lower(C)=lower('"&F10&"') and")

          I’ve modified your Query formula in cell A17 (tab: “kvp”) to incorporate the same.

  5. Hey,

    I have this.

    =QUERY('Raw Data'!A1:T5000,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P where M = '"&W1&"'",1)

    I am trying when W1 is empty, the query shows all information from the database.

    Could you help me? Please.

    • Hi,César,

      You can include the IF as below in your Query formula.

      =QUERY('Raw Data'!A1:T5000,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P"&if(len(W1)," where M = '"&W1&"'",),1)

  6. Hi,

    I have a dataset and I am trying to use the Query function using dates in my data but unable to filter the data.

    So basically I have two columns – “Date 1” and “Date 2”.

    I want to filter out only those data where “Date 2” is more than one month later date than “Date 1”. Could you please help me with the Query formula?

    • Hi, Puja,

      Let me consider more than one month is equal to Date 1 + 30.

      So I am going to filter out the data if Date 2 is greater than Date 1 + 30.

      For this type of filtering the better function is FILTER. Here is the formula for the range A1:B (A1:A contains Date 1 and B1:B contains Date 2).

      =filter(A:B,A1:A+30 < B1:B)

      For exactly one month, we can use the EDATE in filter as below.

      =filter(A:B,edate(A1:A,1) < B1:B)

  7. Is there a way to have two filters for the data or is this too much for google sheets? For example in clothing data color (blue, red) and type (pants, socks).

    In your example, I can only make one filter.

    • Hi, Keaton Hulme-Jones,

      You should use one more drop-down and one more IF function with the Google Sheets Query.

      To test, I’m adding one more column to my sample data. For that just change the Query Importhtml formula in cell A1 as below.

      =query(importhtml("https://en.wikipedia.org/wiki/List_of_best-selling_fiction_authors","Table",1),"Select Col1,Col7,Col5")

      So we have three columns – Author, Nationality, and Genre.

      We have already one drop-down in cell D2 for the “Nationality” filter. Let’s create one more drop-down in cell E2 with the following list.

      All,Romance,Adventure,Fantacy

      Let’s see how to use multiple IF functions in Google Sheets Query.

      Here is our earlier formula.

      =query(A1:C,"Select A " & IF(D2="All",, "where B = '"&D2&"' "),1)

      Change it as;

      =query({A1:C},"Select * " & IF(D2="All",, "where Col2 = '"&D2&"' "),1)

      (formula#1)

      Let’s write one more Query. This time we will use the criterion in cell E2.

      =query({A1:C},"Select Col1 " & IF(E2="All",, "where Col3 = '"&E2&"' "),1)

      (formula#2)

      In this formula#2, replace A1:C with formula#1, and here is that final formula.

      =query({query({A1:C},"Select * " & IF(D2="All",, "where Col2 = '"&D2&"' "),1)},"Select Col1 " & IF(E2="All",, "where Col3 = '"&E2&"' "),1)

  8. Hi,

    I need the formula to get the result as per the below table. Struggling to get the result of the last column.

    The formula I have used to get the first 6 columns is;

    {query(SALES!A2:Y ,"Select C, D, G, P, Sum(T), datediff(now(), todate(P)) WHERE O ='"&(A1)&"' GROUP BY C, D, G, P Label Sum(T) 'PENDING AMOUNT', datediff(now(), todate(P)) 'OVER DAYS' " ,1)}

    INV.DATE | INVOICE NUMBER | CUSTOMER NAME | Due Date | PENDING AMOUNT | OVER DAYS | Status

    • Hi, Narahari GL,

      I don’t know the type of values in columns C, D, G, and P. You have not mentioned that. So if you share a mockup sheet, I would be able to assist you.

LEAVE A REPLY

Please enter your comment!
Please enter your name here