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 basic...
Another variant
=IF(ISNA(IFERROR(LARGE(A1:A5,1),NA())),"",SUM(A1:A5))
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 |
Another variant
=IF(ISNA(IFERROR(LARGE(A1:A5,1),NA())),"",SUM(A1:A5))
Thank youSergeiBaklan
- 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))
