Blank A cell then no formula in J cell vice versa

FrillyBits

New Member
Joined
Jun 22, 2015
Messages
3
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 ;) :biggrin:
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 :)
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
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.
 

mick0005

Active Member
Joined
Feb 21, 2011
Messages
406
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
LMNO
5Break it downResult
6Step 1Form 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)
7Step 2Formula just to check if A6 is blank=ISBLANK(A6)FALSE
8Step 3SUM accounts=SUM(B6:I6)1353
9Step 4Combine the 3 formulas by pointing to cell references only=IF(N7,"",N8)1353
10
11Step 5Now 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

New Member
Joined
Jun 22, 2015
Messages
3
Thanks so much, I will find time to practice constructing my own formulas :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top