How to SUM or SUMIF, but leave result cell blank on condition

English
#1

I want to SUM, but I want the result cell to be empty if the condition does not hold. (There are many examples on the Internet on how to use SUMIF to consider blank cells in the sum range to be 0 in the summation. This is DIFFERENT from what I want.)

Example:

   A  B  C
1  6  2  4
2  
3  9  6  3

I want to fill all A-column cells with the same SUM formula, so they sum the row. 2+4=6, 6+3=9, and so on. But I want Cell A2 to be blank. I do not want it to be 0. The reason is, I am doing further calculations on column A (say, AVERAGE), which correctly ignore blank cells in their calculation, whereas they INCLUDE zeros, which would give me undesired results.

#2

Only if both B and C are blank you want A blank? How about =IF(AND(B1=0,C1=0),"",B1+C1) ?

But if you want if either are blank then make A blank then =IF(OR(B1=0,C1=0),"",B1+C1)

#3

I see. Well, that’s close, but if B or C have actual true zeroes in them, then I do want them to be summed. Your solution causes real zeros to skip the summation also.

#4

OK. =IF(AND(COUNT(B1),COUNT(C1)),B1+C1,"") ,use OR if only one column has to have a number

1 Like
#10

Thanks. While that did work for me, I went with a slightly more comprehensive solution offered by keme1 below. (I wish I could mark both of your answers as a solution, but it only lets me mark one.)

#5

An=IF(OR(Bn="",Cn=""),"",Bn+Cn)

#6

Good, compact suggestion.

If this is meant for other users than the asker, you may want to add robustness. This usually comes at a cost of increased complexity and reduced readability.

This will fail in the common situation where the user presses the space bar to “clear” a cell, because "" <> " "

The beauty of the COUNT() function in this case is that it only counts cells with number entered (even when it is zero). Text entries (like space) are disregarded.
To count also text (which we don’t want in this case) we use COUNTA().

It can be further simplified. If you want to sum when only one entry is present:

=IF(COUNT(Bn:Cn),SUM(Bn:Cn),"")

If you want to count only when the row is filled (both cells contain an entry):

=IF(COUNT(Bn:Cn)=2,SUM(Bn:Cn),"")

To add robustness, this should work also if you insert additional columns to your range and only want to consider rows which are filled:

=IF(COUNT(Bn:Cn)=COLUMNS(Bn:Cn),SUM(Bn:Cn),"")

5 Likes
#7

«This will fail in the common situation where the user presses the space bar to “clear” a cell, because “” <> " "»
The unfortunated handling in pseudo clearing a cell by setting the [:space:]-charakter into it what is invisible, it isn’t a correct method of working, recognise that “ “ has the [U+0000 0020] hex code and is one of the unvisible signs. I know only the =REGEX()-method to empty a cell, better in setting a visible charakter such as (math.) “0” or “—”.
I think/hope @Pistos know the differences, cause he wrote "blanc cell“.

In testing my short formula above in CALC, both charakters [:space:] " " and math. “0” have no result in cell An. But i prefer in changing my formula to
An=IF(OR(Bn="",Cn=""),"–",Bn+Cn)
so the resulting cell shows me “–” instead of nothing. Even the Matrix will set be bigger with more rows and colums as the example in top, the lowest formula @keme1 is more flexible.

1 Like
#9

Thanks, guys. I went with this:

=IF(COUNT(...)=COLUMNS(...),SUM(...),"")

where the ...s are ranges.

#8

my solution, have a look on #3:
TEST_Matrix berechnen.pdf (63.4 KB)
TEST_Matrix berechnen.ods (15.9 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.