# Blank out cell above if last cell has data

#### Nvablejrg

##### New Member
Good day all,

I am trying to blank out a cell above the last cell with data. I only want the last cell in Column J and K to show the total instead of all the cells with the same information.

I have a running total and the amount is the same from the top of the column. I already have a formula in the column and I want to only see the last cell that has data.

Can this be done with a formula in the cell already or do I have to use conditional formatting?

I have been searching and cannot find what I am looking for.

I am including a snapshot of the columns and one of the columns with the formulas in them.
Please see the columns "J" and "K". This is my running total of "Out" and "In" Items. I know why it has the same number in all the cells is because the formula is in all the cells.
This running total is totaling all the Column E (OUT or IN) with Column G (Amount). They are calculating the total "OUT" only and total "IN" only.

This is the formula in Column J and K
=IF(E3="","",SUMIF(\$E\$3:\$E\$44,"OUT",\$G\$3:\$G\$44))
=IF(E3="","",SUMIF(\$E\$3:\$E\$44,"IN",\$G\$3:\$G\$44))

Thank you all in advance.

Nvablejrg.

#### Attachments

• In_Out Pic.PNG
45.9 KB · Views: 7
• Formula Pic.PNG
66.9 KB · Views: 7

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

##### Well-known Member
Please test these two:
Excel Formula:
``=IF(E3="","",IF(AND(COUNTIF(E3:\$E\$3,"out")=COUNTIF(\$E\$3:\$E\$44,"out"),E3="OUT"),SUMIF(\$E\$3:\$E\$44,"OUT",\$G\$3:\$G\$44),""))``
Excel Formula:
``=IF(E3="","",IF(AND(COUNTIF(E3:\$E\$3,"IN")=COUNTIF(\$E\$3:\$E\$44,"IN"),E3="IN"),SUMIF(\$E\$3:\$E\$44,"IN",\$G\$3:\$G\$44),""))``

##### Well-known Member
Also Test this:
Excel Formula:
``=IF(E3="","",IF(COUNTIF(\$E\$3:E3,"out")+COUNTIF(\$E\$3:E3,"IN")=COUNTA(\$E\$3:\$E\$44),SUMIF(\$E\$3:\$E\$44,"OUT",\$G\$3:\$G\$44),""))``
Excel Formula:
``IF(E3="","",IF(COUNTIF(\$E\$3:E3,"out")+COUNTIF(\$E\$3:E3,"IN")=COUNTA(\$E\$3:\$E\$44),SUMIF(\$E\$3:\$E\$44,"IN",\$G\$3:\$G\$44),""))``

#### Nvablejrg

##### New Member
Also Test this:
Excel Formula:
``=IF(E3="","",IF(COUNTIF(\$E\$3:E3,"out")+COUNTIF(\$E\$3:E3,"IN")=COUNTA(\$E\$3:\$E\$44),SUMIF(\$E\$3:\$E\$44,"OUT",\$G\$3:\$G\$44),""))``
Excel Formula:
``IF(E3="","",IF(COUNTIF(\$E\$3:E3,"out")+COUNTIF(\$E\$3:E3,"IN")=COUNTA(\$E\$3:\$E\$44),SUMIF(\$E\$3:\$E\$44,"IN",\$G\$3:\$G\$44),""))``

I tried all of the formulas you provided and but they do not calculate. They do delete all numbers above the last used cell, but it is a ZERO.

Nvablejrg

##### Well-known Member

They worked all. Please upload your file with XL2BB (at the above of post reply section) to see what is error?
Also take screenshot at result of formula.

#### Nvablejrg

##### New Member
They worked all. Please upload your file with XL2BB (at the above of post reply section) to see what is error?
Also take screenshot at result of formula.
I went over the formula and my spreadsheet.

I had tweaked my original by moving columns and that is why I was getting a ZERO. After reviewing the formula I moved the reference to the correct column and it calculated.

I also like the way the calculation reflects at the respective last IN or OUT Row.

Your solution is PERFECT. Thank you very much. This cleans up my spreadsheet and allows for a quick acquisition of numbers of "In" and "Out".

##### Well-known Member
You're welcome & Good luck.

Replies
2
Views
69
Replies
3
Views
96
Replies
5
Views
230
Replies
2
Views
106
Replies
3
Views
125

1,128,127
Messages
5,628,860
Members
416,345
Latest member

### 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?

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