Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 365K Members
- 8,682 Online
- 360K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Count number of blank cells till last non empty cell in a column.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## Count number of blank cells till last non empty cell in a column.

- Home
- :
- Excel
- :
- General Discussion
- :
- Count number of blank cells till last non empty cell in a column.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Saurabh Khanna

Occasional Contributor

03-18-2018
08:37 AM
- last edited on
07-25-2018
11:23 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018
08:37 AM
- last edited on
07-25-2018
11:23 AM
by
TechCommunityAP

Hello -

I want to count number of blank cells till last non empty cell in a column but could not figure out how to do this, can you please help?

When I use **Countblank** formula, it takes an argument as range which should be known in the beginning itself which is a problem because then it becomes manual work to mention the range everytime as my column data (number of rows) can change many times, and different columns can have different number of rows.

Is there a way to mention end limit of the range as row number of last non empty cell in that column?

Thank you in advance,

Saurabh Khanna.

Labels:

23 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018 09:44 AM

Hi Saurabh,

My suggestion is set the range for one time, and make this range expandable somehow so that if you add a new record, the range expands to include this new record.

The way to do that is to convert the normal range to Table and refer COUNTBLANK to a column in this table!

Please find the attached file to test this suggestion.

- Tags:
- COUNTBLANK function

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018 10:10 AM - edited 03-18-2018 10:13 AM

Thank you Haytham, but unfortunately I will not be able to use this exactly the way in which I want to use.

However, I thought of following but failed on below also, just see if you have solution to my below question if you would have encountered.

I tried capturing the last row number of the non empty cell with the below formula (considering maximum 6000 records that I will have at any time).

=SUMPRODUCT(MAX((A1:A6000<>"")*ROW(A1:A6000)))

Let us assume with above formula I get value as 390.

Now, with above formula I am getting the last non empty row number of a column.

No I want to pass this value from above formula into the Countblank function for the second half but only for the row number.

For example:

Countblank(A1:A**390**)

In above formula I want to pass 390 number as a result of SUMPRODUCT function used above.

I tried using the INDIRECT function for passing 390 value, but do not want to fix the column name because INDIRECT function requires column name to be fixed.

Thus not able to solve my issue.

Any idea?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018 10:17 AM

You can try taking advantage of Index function

Your dynamic range should be =$A$1:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A))))

Your dynamic range should be =$A$1:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018 10:22 AM - edited 03-18-2018 10:23 AM

Saurabh,

You can join the value of SUMPRODUCT to the range "A1:A" and then convert the result to an actual range by using INDIRECT as follows:

=COUNTBLANK(INDIRECT("A1:A"&SUMPRODUCT(MAX((A1:A6000<>"")*ROW(A1:A6000)))))

I hope that helps

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018 01:17 PM

If you have many formulas then I don’t recommend using INDIRECT function, as it will slowdown your workbook. INDIRECT Is a volatile function.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 02:27 AM

Thank you Haytham, I had tried below formula earlier, and that is the issue which I had mentioned in my previous usage of INDIRECT function that column names are fixed and no longer remains dynamic.

Like in below example column A that is "A1:A" is fixed in this formula under the INDIRECT function. Although this formula works perfectly Ok for column A, but if I want to paste this formula for many other columns then I have to manually change this input under the quotes for every column and that is where I have an issue with this formula, it no longer remains dynamic and does not change column reference when pasting in other columns, if you can guide me on how to resolve this issue, then it perfectly solves my issue :) and I will be very happy to learn this :)

=COUNTBLANK(INDIRECT("A1:A"&SUMPRODUCT(MAX((A1:A6000<>"")*ROW(A1:A6000)))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 03:08 AM

you can use my formula and then when you drag it to the left, it will also change the reference, as the formula uses relative references.

=COUNTBLANK(A$1:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 03:08 AM

As variant that could be

=COUNTBLANK(OFFSET(A1,0,0,AGGREGATE(14,6,(A:A<>"")*ROW(A:A),1)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 04:19 AM

Hi Saurabh,

My friends in previous replies give you some supposed solutions.

Please test their solutions.

Each formula gives you the same result, except if the whole column is empty!

In this case, @Sergei Baklan's formula returns a **#REF!** error, but @Jamil Mohammad's formula counts all cells in the column which are all blanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 04:37 AM

@Haytham Amairah, thank you. When with wrap

=IFERROR(COUNTBLANK(OFFSET(B1,0,0,AGGREGATE(14,6,(B:B<>"")*ROW(B:B),1))),0)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 09:06 PM - edited 03-19-2018 09:08 PM

Hello Mohammad -

I agree with you, your formula gives the desired result, but when I try to use this formula in the same column in which I have the data I am getting an error, maybe because I am not able to modify the formula as per my need.

I am attaching the excel sheet, were I have mentioned your formula in column other than A (in cell B2 highlighted in green color), but I want formula to work in the same data column that is in cell A2 highlighted in yellow color.

I tried using the formula with couple of modifications but failed, can you guide me, maybe I am missing on using it properly :(

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018 11:33 PM

Solution
It does not work because of circular reference problem.

My formula range starts from A1 while you also want to use inside the same range.

To make this work you need to redefjne the starting row of your column A in the formula for example if your start of data starts from A4 then the modified formula should be

=COUNTBLANK(A$4:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))))

This should work.

My formula range starts from A1 while you also want to use inside the same range.

To make this work you need to redefjne the starting row of your column A in the formula for example if your start of data starts from A4 then the modified formula should be

=COUNTBLANK(A$4:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))))

This should work.

Best Response confirmed by
Jamil Mohammad (Super Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-05-2018 11:08 AM

Hello Jamil -

Your suggestion worked for me to a great extent.

I was trying to post for last couple of days but was not able to post.

And I was using your listed suggestion for my work.

I have one small help again from you, attached is the excel sheet in which I am using your suggestion, however, I am able to use formula not in the same column in which count has to happen, I am able to use formula in cell other than column in which it is required.

For example, I have data in column A but formula I am able to use for Column A is in Column B.

Is it possible to use the formula in the same column in which the data is?

Thank you for your help.

Saurabh Khanna.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-05-2018 11:47 AM - edited 05-05-2018 11:56 AM

=COUNTBLANK(A$4:INDEX($A4:$A$500000,SUMPRODUCT(MAX(($A4:$A$500000<>"")*(ROW($A4:$A$500000)-ROW($A$4)+1)))))

or

=COUNTBLANK(OFFSET(A$4,0,0,AGGREGATE(14,6,1/($A4:$A$500000<>"")*(ROW($A4:$A$500000)-ROW($A$4)+1),1)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018 09:34 PM

Hello -

I am able to get the desired results and formula also works if i Past it for any column.

However, I noticed that if there is no data in a specified range for that column, then it does not work properly, can there be a way to treat this situation of no data as an exception?

Attaching the reference sheet with an example scenario.

Thank You,

Saurabh.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 06:59 AM

Hi Saurabh,

this is the formula to put in A2 and drag to the right .

Please note that it is an array formula, so it must be entered with holding Control+Shift+Enter.

=IFNA(COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>"")))),0)

see attached workbook

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 04:20 PM

Or

=IFNA(COUNTBLANK(A3:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))),0)

(regular formula)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 02:01 AM

Hello -

When I downloaded your file, I am not getting result, it shows error.

I have taken screenshot "1" when file is downloaded and opened, and "2" when I pasted the formula in the cell.

Am i doing something wrong at me end, or is it Excel version compatibility issue; I am using Excel 2010?

Thank You,

Saurabh Khanna.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 02:04 AM

Hello -

When I downloaded your file, I am not getting result, it shows error.

I have taken screenshot "A" when file is downloaded and opened, and "B" when I pasted the formula in the cell(by Ctrl+Shift+Enter).

Am i doing something wrong at me end, or is it Excel version compatibility issue; I am using Excel 2010?

Thank You,

Saurabh Khanna.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 02:40 AM

it is because you are probably using Excel 2010 which does not have IFNA function.

SO i replaced is with IF ISNA

like this

it is again with Control shift enter. see attached

=IF(ISNA(COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>""))))),0,COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>"")))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 02:48 PM

You may change IFNA on IFERROR

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 10:49 PM

Many Thanks Jamil for your help, it works great.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 10:49 PM

Many Thanks Sergei for your help, it works great.

Related Conversations

How to count and sum "Condtional formatting" cells by color in Excel 2010?

Deleted
in
Excel
on
10-12-2017
58K
Views

0 Likes

93 Replies

Count entries on a category omitting duplicates and blank cells

Vicente Heyermann Vial
in
Excel
on
03-16-2018
1,431
Views

0 Likes

9 Replies

Add 5 if a cell contains text "yes"

Titchard Family
in
Excel
on
11-30-2017
830
Views

0 Likes

8 Replies

Count dates in multiple worksheets and display in a summary sheet

sbeardsley51
in
Excel
on
04-13-2019
109
Views

0 Likes

1 Replies

AADConnect cn attribute and group member count

Himanshu Singh
in
Azure Active Directory
on
02-20-2019
215
Views

0 Likes

5 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft