Forum Discussion
Solved
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?
Another variant
=IF(ISNA(IFERROR(LARGE(A1:A5,1),NA())),"",SUM(A1:A5))
9 Replies
=if(sum(J6:J9)=0,"",Sum(J6:J9))
- 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.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
