Need help with SUM formula (multiple cells) that shouldn't leave result trail

Hello again,

My first problem was solved by ACE.

Here is second one:

So this is what I am looking to achieve. In my file, that is attached, I want the Total column (G) to display the SUM values only when the three columns D+E+F is input (or any left blank). So right now G8:G46 is displaying the value fully in a long line going down. I don't want that. I want the formulas to stay in place from G8:G46 but only want the TOTAL value to display ONLY AFTER the columns D+E+F values are put in. So G10 should be invisible (keeping the sum formula in there). And only after I input the D10,E10,F10 (or any two or any one) value should the G10 display the sum of G9+D10+E10+F10.

Is there anyway that I can do that? Or do I have to live with the long list of "40" that you currently see in TOTAL column (G)?


Thanks!!!

=CHOOSE(AND(D8="",E8="",F8="")*1 +1,SUM(D8:F8,G7),"")

copy that down the column :D - see attachment.


you could also do this with conditional formatting if you like...

In G9

=IF(COUNT(D9:F9)=3,G8+SUM(D9:F9),"")

Copy down

hmm. you need to avoid blanks in the rows for this to work.

look at the cells i highlighted for you in the L column, those numbers are wrong because there is no number above them.

Could work out a couple of other fancy solutions to fix it (conditional formatting, or more confusing functions to find the previous value....) but really, if you just avoid "gaps" in the data you will be fine.

Just realized something simple :D - See the attachment.

If you use this, there is no "gap" problem. This would need to be put in the 7th row, and copy/pasted down.

=CHOOSE(AND(B7="",C7="",D7="",I7="",J7="")*1+1,SUM(B$7:B7,D$7:D7)-SUM(C$7:C7,I$7:I7,J$7:J7),"")

=CHOOSE(AND(B9="",C9="",D9="",I9="",J9="")*1+1,SUM(N$7:N9,P$7:P9)-SUM(O$7:O9,S$7:S9,T$7:T9),"")

That section I highlighted is the key idea. This formula is "turning off" the summation when there are blanks in those cells. If you want both U and L to be blank or not blank at the same time (which is what your comment indicates), then used the same AND() function in both columns.


See attached.


BTW, this is what the function is doing.

CHOOSE( 1 or 2, show summation if 1, nothing if 2)

IF you have blanks in the b,c,d,i,j columns for a row, then the AND function becomes AND(TRUE,TRUE,TRUE,TRUE,TRUE) which evaluates to TRUE. Basically, and AND function only returns the value, TRUE or FALSE. True is returned ONLY when all of the arguments are TRUE. Even if you have 1000 arguments, 999 of which are true, if 1 of them is false the entire AND() function is false.


Also. When you multiply by TRUE or FALSE excel treats these boolean values (look up boolean logic) as 1 and 0 respectively. SO, TRUE*1 = 1 and FALSE*1 = 0

That's how I know the choose function index (the first part of the function argument list) will always evaluate to 1 or 2. Because, TRUE*1+1 = 2 and FALSE*1+1 = 1

:D :D :D

...the more you know...

great to hear , thank you.