Count blank rows

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
999
Office Version
  1. 365
Platform
  1. Windows
I have data in column E with no blank entries from E4 to currently E224.
When the job in hand has been done I enter a date in column D.

I would like a formula in D 250 which would find the last row in column E with data in it currently E224 and then count the number of rows in column D that are blank.

I hope this makes sense and as ever your help is appreciated.

EDIT: I should add that the position of the formula will move down as records are added
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,544
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=LOOKUP(2,1/(E4:E249<>""),ROW(E4:E249))-LOOKUP(2,1/(D4:D249<>""),ROW(D4:D249))
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
999
Office Version
  1. 365
Platform
  1. Windows
Thank you for your response however it doesn’t appear to be finding any blank rows in column D
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,544
Office Version
  1. 365
Platform
  1. Windows
I think I may have miss-understood, are you trying to find the number of blanks in col D below the last date, or the total number of blanks?
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
193
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is it correct that you'd like the total of rows above 250 where column E is filled but column D is not? Then
Excel Formula:
=SUM(--ISBLANK(FILTER(D2:D249,LEN(E2:E249)>0)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,544
Office Version
  1. 365
Platform
  1. Windows
Another option for total blanks in D
Excel Formula:
=COUNTIFS(E$4:E249,"<>",D$4:D249,"")
 
Solution

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
999
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think the latest version will work (but what do I know) but the system I’m on at the moment hasn’t been upgraded so I‘ll give it a go on my home machine shortly and report back.
Again sincere thanks for taking the time
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
999
Office Version
  1. 365
Platform
  1. Windows
Fluff I’m sorry I didn’t realise that there was a new member helping me.
However im pleased to say that they both work fine.

So Fluff and Habest my sincere thanks to you both for taking the time to help me
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,544
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,614
Messages
5,707,410
Members
421,508
Latest member
Jalayne

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