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

[default_name]

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:

=SUMIF(range,criteria,[sum_range])

Here is the formula I am currently working with:

=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

 

[Olukunle]

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:

=SUMIF(range,criteria,[sum_range])

Here is the formula I am currently working with:

=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]

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]

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?

 

[Fluff]

Does col L have a formula, if so what?

 

[default_name]

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]

How about

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

 

[default_name]

How about

=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]

Maybe try SUMIFS?

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

 

[Fluff]

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.