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

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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?

Thanks in advance
 
Last edited:

Excel Facts

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

Olukunle

New Member
Joined
Oct 31, 2012
Messages
16
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?

Thanks in advance
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
Joined
Jun 12, 2014
Messages
56,817
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
56,817
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does col L have a formula, if so what?
 

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
56,817
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
How about
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!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Maybe try SUMIFS?

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,817
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top