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.