**IF** is one of the most useful and widely used functions of Excel. Today I will be showing how to 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, numbers, 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**

**Case 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:

- The 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`

,

**Case 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 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.