Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Countblank in reference to another column

0

I have a spreadsheet that has claims that I have to do accounts on.  After I open the accounts I put in the account number.

The claim number is in the B column and the account number is in the A column.  What I need to do is have countblanks for the B column based on A column as I put in the account numbers the countblank number should go down.

So if I have 10 claims and there are no account numbers I want to show a 10 in the amount of countblanks.  As I put in the account numbers the number 10 should go down 9, 8 ,7....till zero.

If I use the A column it won't work because the whole column has blank cells.  If I use the B column it won't work because they are all non blank.  So I need to determine how many are blank based on both columns.

I don't know if that makes sense.  Any help is appreciated.

Thanks

excel
MS Office Version: 2016
posted by: LymaBeane (rep: 2) on Fri Apr 28, 2017 at 9:54 am
Answer
Discuss

Answers

0

I don't fully understand your intention, but would something like this help?

=IFERROR(MATCH(10^12,$B:$B)-MATCH(10^12,$A:$A),"")

The formula will determine the last used cell in column B and deduct from that number the last used cell in column A. If an error occurs in this attempt the result will be "". An error will occur if the last value in eitehr column isn't a number. The formula will return a wrong result if you have more than a million rows in your worksheet.

posted by: Variatus (rep: 4889) on Fri Apr 28, 2017 at 10:49 am
Discuss

Discussion

It didn't work. Oh well back to the drawing board. Thanks anyway. 
LymaBeane (rep: 2) Apr 28, '17 at 2:01 pm
"It didn't work"? I assure you it was tested and worked. However, I regret that it wasn't what you needed. Have another try at describing what you need perhaps, this time, starting with what the formula I submitted didn't do.
Variatus (rep: 4889) Apr 28, '17 at 9:17 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login