# Ignore Blank Cells in the [Sum Range] of a SUMIF

#### default_name

##### Board Regular
Hey guys,

I have looked online and have found some similar questions related to this, but nothing I have found seems to work.
I am trying to use a SUMIF statement on a large collection of data.
For the most part, it works just fine.
However, if there ever happens to be a blank cell in the [sum_range] variable then the entire formula gets botched, resulting in an #N/A

For reference:
Code:
``=SUMIF(range,criteria,[sum_range])``

Here is the formula I am currently working with:
Excel Formula:
``=SUMIF(\$I\$15:\$I\$20000,\$I9,L\$15:L\$20000)``

So basically, if there happens to be a blank cell anywhere between L15:L20000 then the function throws an #N/A
How can I fix this so that blanks/empty cells are ignored. Is that even possible?

Last edited:

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Olukunle

##### New Member
Hey guys,

I have looked online and have found some similar questions related to this, but nothing I have found seems to work.
I am trying to use a SUMIF statement on a large collection of data.
For the most part, it works just fine.
However, if there ever happens to be a blank cell in the [sum_range] variable then the entire formula gets botched, resulting in an #N/A

For reference:
Code:
``=SUMIF(range,criteria,[sum_range])``

Here is the formula I am currently working with:
Excel Formula:
``=SUMIF(\$I\$15:\$I\$20000,\$I9,L\$15:L\$20000)``

So basically, if there happens to be a blank cell anywhere between L15:L20000 then the function throws an #N/A
How can I fix this so that blanks/empty cells are ignored. Is that even possible?

Having blank cells in your range should not throw an #N/A error unless something is wrong or is not really a blank cell. It might be a cell with a Tab/Space character which is considered as a text or something else. Please check the cell very well.

#### Fluff

##### MrExcel MVP, Moderator
Make sure that none of the cells in col L are returning #N/A possibly being hidden by cell formatting or conditional formatting.

#### default_name

##### Board Regular
Aha! You guys were right!
There are, indeed, a handful of #N/A cells hidden (due to conditional formatting) in the sum_range (column L).

Upon additional inspection of those hidden #N/A cells I noticed that they are not true error cells...but they just contain the text "#N/A"

Is there any way to ignore those cells in a SUMIF?

Last edited:

#### Fluff

##### MrExcel MVP, Moderator

Does col L have a formula, if so what?

#### default_name

##### Board Regular
Does col L have a formula, if so what?
Hey Fluff.
Looks like I updated my previous comment at the same time you posted your above response/question.

Upon additional inspection of those hidden #N/A cells I noticed that they are not true error cells...but they just contain the text "#N/A"

#### Fluff

##### MrExcel MVP, Moderator

Excel Formula:
``=SUMIFS(L\$15:L\$20000,\$I\$15:\$I\$20000,\$I9,L\$15:L\$20000,"<>#N/A")``

#### default_name

##### Board Regular
Excel Formula:
``=SUMIFS(L\$15:L\$20000,\$I\$15:\$I\$20000,\$I9,L\$15:L\$20000,"<>#N/A")``
YES!!!
Works perfectly!

Thanks Fluff!

The title of this thread should've been "Ignore Cells With Text in the [Sum Range] of a SUMIF"
Hopefully this comment will help any future excel users with the same issue

You're awesome!

##### Well-known Member
Maybe try SUMIFS?

`=SUMIFS(L\$15:L\$20000,L\$15:L\$20000,"<>#N/A",\$I\$15:\$I\$20000,\$I\$9)`

#### Fluff

##### MrExcel MVP, Moderator
Even that title would be misleading as the formula does ignore text, it's the error values it didn't like.
Glad we could help & thanks for the feedback.

Replies
3
Views
205
Replies
1
Views
78
Replies
7
Views
168
Replies
15
Views
149
Replies
8
Views
137

1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back