• 469K Members
• 5,210 Online
• 568K Conversations
SOLVED

New Contributor

# Sumif not blank

Hello!

I want to use a formula to ignore blank cells when i sum blank spaces but if I use "Sum(J6:J9)" it will give me a "0" even if there's no values in the cell, and what i'm trying to do is basically this: "if(sum(J6:J9)="","",Sum(J6:J9))" but the formula is still taking a blank space as "0" so it will give me again "0" when i'm trying to ignore those blank spaces.

Any ideas?

8 Replies

# Re: Sumif not blank

Hello Luis,
It appears that you want the result to appear as an EMPTY TEXT (“”), if the sum range solely consists of blank cells. This formula will return what you want:
=IF(SUM(J6:J9),
SUM(J6:J9),
“”)
Note that a 0 result of the logical_test argument of the IF function is equivalent to FALSE. Conversely, a non-zero result thereof is equivalent to TRUE.
In plain words, the formula means that if the sum is not 0, return that sum; otherwise, return an EMPTY TEXT (“”).
For distinction, empty text is text with 0 length while blank cell is cell without any content. Empty text is not, but is counted as, blank.

# Re: Sumif not blank

`=if(sum(J6:J9)=0,"",Sum(J6:J9))`

# Re: Sumif not blank

Hi Twifoo,

Thanks for your fast response, the thing is that, if i use that formula, there's sometimes when the number might be "0", and that's ok for me, but if the cell is blank, the result of that "sum" formula is still giving me "0".

Imagine inside the bottom cell is the "sum" formula.

This is ok

 1 1 0 0 2

This is also ok.

This is not ok.

 0

# Re: Sumif not blank

Hello Luis,
Assuming your range is A1:A5 and you want the result to appear in A6, your formula in A6 is:
=IF(COUNTIF(A\$1:A5,"")=ROWS(A\$1:A5),
"",
SUM(A\$1:A5))
Corollary to my earlier statement, Blank Cells are not, but are counted as, Empty Text (""), such that this formula, although longer, achieves the same result:
=IF(SUMPRODUCT(--ISBLANK(A\$1:A5))=ROWS(A\$1:A5),
"",
SUM(A\$1:A5))
Solution

# Re: Sumif not blank

Another variant

`=IF(ISNA(IFERROR(LARGE(A1:A5,1),NA())),"",SUM(A1:A5))`

# Re: Sumif not blank

Thank you so much! this work
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies