If you are looking for some of the easiest ways to ignore blank cells in a range in Excel for getting perfect results with formulas, then you will find this article useful. So, let’s start our main article.

**Table of Contents**hide

## Download Workbook

## 8 Ways to Ignore Blank Cells in Range in Excel

Here, we have the following dataset containing the ** Selling Prices **and

**of some products of a company. We will show the ways to ignore the blank cells in the range of the**

*Cost Prices***Selling Price**column.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Ignore Blank Cells in Range by Using the IF Function

Here, we will use the **IF function** to calculate the *Profit Margin *of the products ignoring the blank cells of the **Selling Price **column. If we don’t ignore these blank cells here then we will get errors and to avoid this error ignoring blank cells is essential.

Let’s try not to ignore the blank cells at first for calculating *Profit Margin*.

➤ Enter the following formula in cell **E4 **and drag down the **Fill Handle **tool.

`=(C4-D4)/C4`

Here, **C4 **is the **Selling Price**, **D4 **is the **Cost Price**.

** **

So, we are having **#DIV/0! **error for the blank cells in the **Selling Price **column.

To solve this problem, we will use the following formula to ignore the blank cells

`=IF(C4="","",(C4-D4)/C4)`

When **C4 **will be blank it will return **TRUE**, then **IF **will return a blank otherwise we will get the *Profit Margin*.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the profit margins of the products ignoring the blank cells of the **Selling Price **column.

To have a better visualization let’s format the *Profit Margin* column. After adding *Percent Style*, we are getting the following *Profit Margins *of the products.

**Read More:** **How to Remove Blank Cells in Excel (10 Easy Ways)**

__Method-2__: Using the ISBLANK Function to Ignore Blank Cells in Range in Excel

In this section, we will be using the **ISBLANK function** for ignoring the blank cells while calculating the *Profit Margin *of the **Selling Price **column.

** Steps**:

➤ Type the following formula in cell

**E4**.

`=IF(ISBLANK(C4),"",(C4-D4)/C4)`

**ISBLANK(C4) →**returns**TRUE**for the blank cells and**FALSE**for the non-blank cells.**Output →**FALSE

**(C4-D4)/C4 →**gives the*Profit Margin*for*Apple***Output →**0.439

**IF(ISBLANK(C4),””,(C4-D4)/C4)**becomes**IF(FALSE,””,0.439) →**returns the value 0.439 because the condition is giving**FALSE****Output →**0.439

➤ Press **ENTER **and drag down the **Fill Handle **tool.

After that, you will get the ** Profit Margins **for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the ** Percent Style **to the

**Profit Margin**column.

**Read More:** **Find If Cell is Blank in Excel (7 Methods)**

__Method-3__: Using the ISNUMBER Function

You can use the **ISNUMBER function** to calculate the *Profit Margins *for the products excluding the products having no *Selling Prices*.

** Steps**:

➤ Type the following formula in cell

**E4**.

`=IF(ISNUMBER(C4),(C4-D4)/C4,"")`

**ISNUMBER(C4) →**returns**TRUE**for the numbers otherwise**FALSE**(for checking texts you can use the**ISTEXT function**similarly)**Output →**TRUE

**(C4-D4)/C4 →**gives the*Profit Margin*for*Apple***Output →**0.439

**IF(ISNUMBER(C4),(C4-D4)/C4,””)**becomes**IF(TRUE, 0.439,””) →**returns the value 0.439 because the condition is giving**TRUE****Output →**0.439

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the ** Profit Margins **for the products ignoring the blank cells of the

**Selling Price**column.

After applying the ** Percent Style **we will get the

*Profit Margins*as below.

**Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)**

__Method-4__: Using the COUNT Function to Ignore Blank Cells in Range in Excel

Here, we will use the **COUNT function** to calculate the *Profit Margins *of the products ignoring the blank cells of the **Selling Price **column.

** Steps**:

➤ Type the following formula in cell

**E4**.

`=IF(COUNT(C4)=1,(C4-D4)/C4,"")`

**COUNT(C4) →**counts the number of cells containing numbers**Output →**1

**COUNT(C4)=1**becomes**1=1**and so returns**TRUE**.**(C4-D4)/C4 →**gives the*Profit Margin*for*Apple***Output →**0.439

**IF(COUNT(C4)=1,(C4-D4)/C4,””)**becomes**IF(TRUE,0.439,””) →**returns the value 0.439 because the condition is giving**TRUE****Output →**0.439

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Afterward, you will get the ** Profit Margins **for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the ** Percent Style **to the

**Profit Margin**column.

**Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)**

**Similar Readings**

**How to Delete Empty Cells in Excel (6 Methods)****Remove Blank Lines in Excel (8 Easy Ways)****Fill Blank Cells with Value Above in Excel (4 Methods)****Excel VBA: Check If Multiple Cells Are Empty (9 Examples)****How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)**

__Method-5__: Ignore Blank Cells in Range by Using the COUNTA Function

In this section, we will calculate the *Profit Margins *of the products using the **COUNTA function** only for the products having values in the **Selling Price **column.

** Steps**:

➤ Type the following formula in cell

**E4**.

`=IF(COUNTA(C4)=1,(C4-D4)/C4,"")`

**COUNTA(C4) →**counts the number of cells containing numbers and texts**Output →**1

**COUNTA(C4)=1 →**becomes**1=1**and so returns**TRUE**.**(C4-D4)/C4 →**gives the*Profit Margin*for*Apple***Output →**0.439

**IF(COUNTA(C4)=1,(C4-D4)/C4,””)**becomes**IF(TRUE,0.439,””) →**returns the value 0.439 because the condition is giving**TRUE****Output →**0.439

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the profit margins of the products ignoring the blank cells of the **Selling Price **column.

After adding *Percent Style*, we are getting the following *Profit Margins *of the products.

**Read More:** **How to Find Blank Cells Using VBA in Excel (6 Methods)**

__Method-6__: Using the COUNTBLANK Function to Ignore Blank Cells in Range

You can use the **COUNTBLANK function** also to calculate the *Profit Margins *ignoring the blank cells in the range of the **Selling Price **column.

** Steps**:

➤ Type the following formula in cell

**E4**.

`=IF(COUNTBLANK(C4)=0,(C4-D4)/C4,"")`

**COUNTBLANK(C4) →**counts the number of blank cells**Output →**0

**COUNTBLANK(C4)=0 →**becomes**0=0**and so returns**TRUE**.**(C4-D4)/C4 →**gives the*Profit Margin*for*Apple***Output →**0.439

**IF(COUNTBLANK(C4)=0,(C4-D4)/C4,””)**becomes**IF(TRUE,0.439,””) →**returns the value 0.439 because the condition is giving**TRUE****Output →**0.439

➤ Press **ENTER **and drag down the **Fill Handle **tool.

After that, you will get the ** Profit Margins **for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the ** Percent Style **to the

**Profit Margin**column.

**Related Content: Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)**

__Method-7__: Extracting a Range While Ignoring the Blank Cells

Suppose, we want to extract the range of the **Selling Price **column excluding the blank cells and to do this here we will use the **IFERROR function**, **INDEX function**, **SMALL function**, **IF function**, **ISNUMBER function**, **ROW function**. So, we will extract the values from the left **Selling Price **column to the right **Selling Price **column.

** Steps**:

➤ Type the following formula in cell

**F5**.

`=IFERROR(INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))),"")`

returns`ISNUMBER($C$4:$C$14)`

→**TRUE**for the numbers otherwise**FALSE****Output →**`{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}`

returns the row numbers of this range`ROW($A$1:$A$11)`

→**Output →**`{1;2;3;4;5;6;7;8;9;10;11}`

returns the row number of this cell`ROW(A1)`

→**Output →**1

becomes`IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),"")`

returns the row numbers for`IF({TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11},"")`

→**TRUE**otherwise blank**Output →**`{1;2; “”;4;5;6; “”;8;9; “”}`

becomes`SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1)))`

`SMALL(`

`{`

returns the 1st smallest value of this range`1;2; “”;4;5;6; “”;8;9; “”},1)`

→**Output →**1

becomes`INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1)))`

returns the 1st value of this range`INDEX($C$4:$C$14,1)`

→**Output**→ 3663

becomes`IFERROR(INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))),"")`

returns blank for any error`IFERROR(3663,"")`

→**Output**→ 3663

➤ Press **ENTER **and drag down the **Fill Handle **tool.

In this way, you will get the extracted values of the **Selling Price **column ignoring the blank cells.

**Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)**

__Method-8__: Ignore Blank Cells in Range by Using the AVERAGE Function

The **AVERAGE function** counts the average of a range ignoring the blank cells by default, and so, here we will calculate the average of the selling prices excluding the blank cells using this function.

** Steps**:

➤ Enter the following formula in cell

**E5**.

`=AVERAGE(C4:C14)`

It will calculate the average of this range excluding the blank cells.

Now, we can check if the **AVERAGE function **is actually calculating the average excluding the blank cells.

➤ Enter the following formula in cell **E8**.

`=(C4+C5+C7+C8+C9+C11+C12+C14)/8`

Here, **C4**, **C5**, **C7**, **C8**, **C9**, **C11**, **C12**, **C14 **are the non-blank selling prices.

So, we can see that the both average values of the selling prices are the same.

**Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)**

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, we tried to cover the ways to ignore blank cells in a range in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.