Blank out cell above if last cell has data

Nvablejrg

New Member
Joined
Sep 10, 2008
Messages
30
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
    In_Out Pic.PNG
    45.9 KB · Views: 7
  • Formula Pic.PNG
    Formula Pic.PNG
    66.9 KB · Views: 7

Some videos you may like

Excel Facts

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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),""))
 
Solution

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Sep 10, 2008
Messages
30
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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),""))
maadabi,

Thank you for your reply.

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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 10, 2008
Messages
30
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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".
 

Watch MrExcel Video

Forum statistics

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

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