Blank A cell then no formula in J cell vice versa

[FrillyBits]

Hi all
I'm new to Excel and have been lurking here to search for answers but I can't find the answer with a search this time
My sheet is for keeping track of my money and transactions in various accounts
I will try to set out exactly what I want to do.

• Rows 1 2 3 4 5 are locked header with names etc
• Column A is date formatted as 23 Jun 15
• Columns B C D E F G are various accounts and are formatted as number 1,234.56 I prefer number rather than currency because it's easier for me to read without the $
• Columns H and I are formatted as Text and contain a description and notes
• Column J is the total amount of B > G and is formatted as number 1,234.56
At the moment in column J is the formula =SUM($B$6:G6) down to =SUM($B$6:G200)
This formula works well but is not aesthetically pleasing as it fills all the cells down to 200 with the total. I can live with it but my username is an indication of how I like things to be just right if possible
I would like to only see the total in cell J if there is a date in cell A. Example:
A100 has date J100 has total =SUM($B$6:G100)
A100 is blank J100 is blank

I have searched and played around with IF and ISBLANK but I can't find a solution on my own.
Any help is greatly appreciated and I thank you in advance

 

[mikerickson]

Try
=IF(ISNUMBER($A6),SUM($B$6:G6),"")

Or you could use Conditional Formatting to change the font color to white if the cell contents are zero.

 

[FrillyBits]

Try
=IF(ISNUMBER($A6),SUM($B$6:G6),"")

Excellent, works perfectly, thanks so much

....Or you could use Conditional Formatting to change the font color to white if the cell contents are zero.

Very clever suggestion, I will remember that one. Sometimes I overthink and the simple solution evades me

 

[mick0005]

May I suggest in the future, try working out the formula in pieces... you were on the right track with the IF and ISBLANK functions (this is only 1 way to do it, mikerickson also pointed out another). If you can't figure out how to combine the functions to do what you need, do it in separate cells first and then just combine them by pasting the cells into the arguments for the IF.

Like this:

Excel 2010

	L	M	N	O
5		Break it down		Result
6	Step 1	Form If Statement in plain english terms to help you walk through the formula logic (both italics in the formula are just your own description of what u need to do)	=IF(A6 is blank,"",sum accounts)
7	Step 2	Formula just to check if A6 is blank	=ISBLANK(A6)	FALSE
8	Step 3	SUM accounts	=SUM(B6:I6)	1353
9	Step 4	Combine the 3 formulas by pointing to cell references only	=IF(N7,"",N8)	1353
10
11	Step 5	Now you replace the cell references in the formula with the actual formulas in those cell references	=IF(ISBLANK(A6),"",SUM(B6:I6))	1353

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[FrillyBits]

Thanks so much, I will find time to practice constructing my own formulas

 

[mick0005]

No problem. Here is a good video that explains much better what I was trying to say. He does a good job of showing how to break down a complex formula (or any nested function formula) into easy to manage steps.

https://exceljet.net/tips/how-to-build-a-complex-formula-step-by-step