Sumif not blank

[Luis BERNAL]

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?

[#2]

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.

[#3]

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

[#4]

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

[#5]

[#6]

[#7]

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))

[#8 · Accepted solution]

Another variant

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

[#9]

Thank you so much! this work