Formula/Function Required to Count Blank Cells in a Dynamic Range

**MattyD89** · 07-10-2014, 10:48 AM

Good afternoon,

I am having difficulty in creating a formula which will count how many blank cells are in one column. The data and number of rows is constantly increasing and decreasing based on what data is returned.

So for example, cells A1:A30 will contain a mixture of cells containing text and blank cells. Then next month, the cell range could be A1:A45 but containing the same type of data.

I understand that I will need to configure the name of the range to make it dynamic using the offset function, but after that I am drawing a blank on how to execute the formula to count the blanks. Am I getting confused by applying the formula within the offset, or do I need to do it a different way?

Any help would be most appreciated.

Thanks!

Matt

**Tony Valko** · 07-10-2014, 10:56 AM

Try this...

Create the dynamic range.

Name: MyRange (or whatever you want to name it)

Refers to:

=$A$1:INDEX($A:$A,MATCH("zzzzz",$A:$A))

Then, if your data looks like this:

Data Range

	A
1	text
2
3
4	text
5
6
7
8
9	text
10
11
12
13
14
15

To count the empty cells within the dynamic range of MyRange:

=COUNTBLANK(MyRange)

Result = 6

In this case the dynamic range evaluates to A1:A9. From A1 to the last (bottom-most) TEXT entry, cell A9.

**MattyD89** · 07-10-2014, 11:13 AM

Hi Tony,

That is perfect, works exactly as I need it to, so thank you very much!

Quick question, there will be a circumstance where the last cell of the range will be a blank, which I will need it to count. Is there a way around this issue using the formula?

Thanks

Matt

**Vikas_Gautam** · 07-10-2014, 11:33 AM

Hey man,

Got the solution...
It is automatic...
check the attachment...

Say thanks, click *

**Vikas_Gautam** · 07-10-2014, 11:34 AM

Hi Matty,
The above attachment includes the solution to your second query as well...

Click *, If helped...

**Tony Valko** · 07-10-2014, 01:49 PM

Originally Posted by MattyD89

Hi Tony,

That is perfect, works exactly as I need it to, so thank you very much!

Quick question, there will be a circumstance where the last cell of the range will be a blank, which I will need it to count. Is there a way around this issue using the formula?

Is this a special circumstance or should it always apply?

If it should always apply just add 1 to the MATCH function:

=$A$1:INDEX($A:$A,MATCH("zzzzz",$A:$A)+1)

With this data:

Data Range

	A
1	text
2
3
4	text
5
6
7
8
9	text
10
11
12
13
14
15

The dynamic range MyRange now evaluates to A1:A10.

=COUNTBLANK(MyRange) = 7

**MattyD89** · 07-11-2014, 03:33 AM

Hi Tony,

That's great, thank you very much.

It should always apply, but until I receive the data, I don't know how many blanks the range could end with. For example, one week it could be ending in one blank and the next week it could be ending in 4.

Will the change to the formula count just the one extra blank?

Thanks

Matt

**MattyD89** · 07-11-2014, 03:35 AM

Hi Tony,

That's great, thank you very much.

It should always apply, but until I receive the data, I don't know how many blanks the range could end with. For example, one week it could be ending in one blank and the next week it could be ending in 4.

Will the change to the formula count just the one extra blank?

Thanks

Matt

**Tony Valko** · 07-11-2014, 08:46 AM

OK, now I'm officially confused!

I don't understand how you determine a range to be either 1 or 4 empty cells after the last non-empty cell. Is there a non-empty cell in another column that is used to determine the end of the range?

**MattyD89** · 07-11-2014, 09:42 AM

Sorry to confuse you!

An example would be if I was to receive 120 rows of data (A1:A120), the column would contain various text answers with some blank cells where they could not answer. The last 4 or 5 rows of the 120 might not answer and the cells would be blank, but I still need to calculate how many blank cells are in the range (A1:A120). Is this making any more sense? If not I can create an example and upload it, if I can?

Also, this formula will be repeated across other ranges that I will need to calculate blank cells for. I have tried to work with your formula to increase the range (So instead of doing it for 1 column, doing it for 4 or 5) and am having difficulty with trying to expand it to include the range I need it to be.

Any help you can offer would be fantastic, you have already helped substantially so far

.

Matt

**Tony Valko** · 07-11-2014, 09:48 AM

Originally Posted by MattyD89

Is this making any more sense?

Not to me!

If not I can create an example and upload it, if I can?

Yes, that will help. Just make it a SMALL sample file!

**MattyD89** · 07-11-2014, 10:43 AM

Apologies again Tony!

Please see attached, If you need me to explain anything else again, just let me know.

Thanks again!

Matt

**Tony Valko** · 07-11-2014, 11:11 AM

Can you tell me what result you expect based on the sample file?

You have cell borders down to row 16 so I assume you want to count down to that row?

Other than the borders, I don't see anything that can be used to define the range down to row 16. That's 2 rows below the last data cell.

**Tony Valko** · 07-11-2014, 11:25 AM

Or, maybe this is what you want...

Data Range

	A	B	C
1	No	No	Yes
2	No	Yes	Yes
3	------	No	No
4		No	Yes
5	No	Yes	------
6	Yes	------	Yes
7	No	Yes	Yes
8	No		Yes
9	Yes	No	Yes
10	No	Yes	Yes
11		No	No
12	No	No
13	No	Yes
14
15		No

All the columns get counted down to row 15 because row 15 is the last row that contains an entry?

So, count of empty cells is:

A1:A15 = 5
B1:B15 = 3
C1:C15 = 5

**MattyD89** · 07-14-2014, 03:30 AM

Hi Tony,

The expected result would be 12 blanks. I would need it to count to row 16, even if cells A14:C15 in your example contained no data as it would indicate that the question was not answered and needs to be counted as a no answer.

**MattyD89** · 07-14-2014, 03:31 AM

Hi Tony,

The expected result would be 12 blanks. I would need it to count to row 16, even if cells A14:C15 in your example contained no data as it would indicate that the question was not answered and needs to be counted as a no answer.

**samba_ravi** · 07-14-2014, 03:45 AM

find the attached file
one additional column was inserted

**MattyD89** · 07-14-2014, 04:30 AM

That's fantastic, thanks Siva!

Formula/Function Required to Count Blank Cells in a Dynamic Range

LinkBack

Thread Tools

Rate This Thread

Display

Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Re: Formula/Function Required to Count Blank Cells in a Dynamic Range

Thread Information

Users Browsing this Thread

Similar Threads

Formula to Count Consecutive Blank Cells in a Range Depending on Today's Date

[SOLVED] Macro to copy formula to blank cells in a dynamic range

Dynamic Name Range using OFFSET function for to expand the list as and when required

Advanced filter with blank cells / Dynamic named range with blank cells

Formula required to count in a range of cells where the text is within a string

Bookmarks

Bookmarks

Posting Permissions