**IF** is one of the most useful and widely used functions of Excel. Today I will be showing how you can use this **IF** function with a wide range of values, accompanied by some familiar functions like **FILTER**, **COUNTIF **etc.

**Table of Contents**hide

**Download Practice Workbook**

**How to Use the IF Function of Excel with Range of Values**

Here we have a data set with the names, authors, number and price of some books of a bookshop called Kingfisher Bookshop.

Our objective today is to learn how to apply various **IF** statements on a range of cells like this.

**1. Checking If a Range of Cells Contains a Value or Not**

First of all, we will learn how to check if a range of cells contains a certain value or not.

Let’s check whether there is any book by the author Emily Bronte or not.

That means, whether the column **Author** (column **C**) contains the name Emily Bronte or not.

You can use a combination of **IF** and **COUNTIF** functions of Excel to do that.

Select a new cell and enter this formula:

`=IF(COUNTIF(C4:C20,"Emily Bronte")>0,"There is", "There is Not")`

See, we have got the result as “There is”.

Because there is indeed a book by Emily Bronte on our list. That is “Wuthering Heights”.

**Explanation of the Formula**

returns the number of times the name “Emily Bronte” appears in the range`COUNTIF(C4:C20,"Emily Bronte")`

**C4:C20**.returns`COUNTIF(C4:C20,"Emily Bronte")>0`

**TRUE**if the name appears at least once in the range, and returns**FALSE**if the name doesn’t appear.- Therefore
returns “There is”, if the name appears at least once, and returns “There is Not” if the name does not appear.`IF(COUNTIF(C4:C20,"Emily Bronte")>0,"There is", "There is Not")`

**Notes**

**COUNTIF**function searches for a case-insensitive match. That is, if you use the formulait will still return “There is”.`IF(COUNTIF(C4:C20,"emily bronte")>0,"There is", "There is Not")`

,- If you want an approximate match, you can use
**Wildcard Characters (*,?,~)**within the**COUNTIF**function.

For example, to find out whether there is any book by the Bronte sisters (Both Emily Bronte and Charlotte Bronte), use this formula:

`=IF(COUNTIF(C4:C20,"*Bronte")>0,"There is", "There is Not")`

See, we have got “There is”.

Because there are three books written by the Bronte Sisters.

**2. Creating a List of Values between Two Numbers**

Now we shall apply another **IF** statement.

We will create a list of values from a range that falls between two given numbers.

Let’s find out the prices from column **E** that fall between **$20.00** to **$30.00**.

These types of tasks can be accomplished using the **IF** function of Excel, but it is better to use the **FILTER** function instead.

Select a new cell and enter this formula:

`=FILTER(E4:E20,(E4:E20>=20)*(E4:E20<=30))`

See, we have got a list of all the prices that are between $20.00 and $30.00.

**Explanation of the Formula**

returns 1 when any price is greater than or equal to $20.00 and less than or equal to $30.00, otherwise it returns 0.`(E4:E20>=20)*(E4:E20<=30)`

returns the corresponding value from the range`FILTER(E4:E20,(E4:E20>=20)*(E4:E20<=30))`

**E4:E20**if**(E4:E20>=20)***returns 1, otherwise it returns nothing.`(E4:E20<=30)`

**Note**

To get the names of the books that have prices between $20.00 and $30.00 other than the prices themselves, use this formula:

`=FILTER(B4:B20,(E4:E20>=20)*(E4:E20<=30))`

**3. IF Statement with Multiple AND/OR Conditions**

**3.1 AND Type Conditions**

Now we will apply multiple conditions within an **IF** function.

Let’s check whether each of the books meets two given conditions or not:

- Number of books is at least 15 or not.
- Price is at least $20.00 or not.

Select a new cell adjacent to the first book and enter this formula:

`=IF(((D4>=15)*(E4>=20))=1,"Satisfy","Does not Satisfy")`

Then drag the **Fill Handle** to fill the rest of the cells.

**Explanation of the Formula**

returns 1 if both the conditions are fulfilled, otherwise it returns 0.`((D4>=15)*(E4>=20))`

**IF**returns “Satisfy” if`(((D4>=15)*(E4>=20))=1,"Satisfy","Does not Satisfy")`

otherwise it returns “Does not Satisfy”.`((D4>=15)*(E4>=20))=1`

,

**3.2 OR Type Conditions**

Now come to OR type conditions. What will be the formula to check whether each of the books satisfies at least one condition or not?

Just replace the “*” sign with a “+” and the “=1” with “>=1”.

`=IF(((`

`D4`

`>=15)+(`

`E4`

`>=20))>=1,"Satisfy","Does not Satisfy")`

See, we have identified for each book whether it satisfies at least one condition or not.

**4. Nested IF Conditions**

This is our last task today. We shall use nested **IF** conditions.

That means, we shall apply one** IF** formula within another **IF** formula.

Let me ask you to perform a job. For all the books, check whether the price is greater than or equal to $30.00 or not first.

If yes, check if the number is greater than or equal to 15 or not.

If still yes, check if the author’s name starts with the letter “C” or not.

If still yes, return “Satisfy”.

Otherwise, return “Does not Satisfy”.

This is an example of nested **IF** conditions.

The formula will be:

`=IF(E4>=30,IF(D4>=15,IF(LEFT(C4,1)="C","Satisfy","Does not Satisfy"),"Does not Satisfy"),"Does not Satisfy")`

Enter this formula in the first cell and then drag the **Fill Handle**.

See, only one book satisfies all the three conditions simultaneously.

**Conclusion**

Using these methods, we can use the **IF** function of Excel with any range of cells to check single, multiple, or nested conditions. Do you have any questions? Feel free to ask us.