How do I count blank cells in a column since the last entry?

[Alan L 185]

I want to Count blank cells in a column since the last entry.

A simple example of what I’m trying to get is,

A B C D E
1 2 5 1 4
2 3 1 2 5
3 1 4 1
4 2 6
5 6


3 0 4 1 2


So, the count at the bottom of Column A = 3, bottom of Column B = 0, bottom of Column C = 4, bottom of Column D = 1 and bottom of Column E = 2

[ajryan88]

Hi Alan,

I'm sorry, but I'm not sure I can make any sense of what it is that you are trying to do, as your sample data would indicate to me that column A and column B should have the same number of blank cells since the last entry, as both columns have the same number of rows of data...???

Without knowing exactly what you're after, or the exact layout of your actual data, I would suggest looking into the COUNTBLANK function that is built into Excel.

If this doesn't help you, please upload a sample workbook containing some sample data, the results that you are expecting, and how you expect to obtain those results.

I hope this helps

[Alan L 185]

Sorry, this is the first time I've used the forum. The sample data has moved over when it was posted. The numbers in the first column are supposed to be the row numbers.

Column A should be: 2, 3, 1, 2, 6
Column B should be: 5, 1, 4, 6

[Tony Valko]

Try this...

Entered in say, A10 and copied across:

=COUNTBLANK(INDEX(A1:A5,MATCH(1E100,A1:A5)):A5)

If there is no number entered in a column then the formula will return an #N/A error.

[ajryan88]

Sorry Alan, but I still don't understand. What row do you want to enter in the formula to calculate the number of blank cells? And how do you get 3 in column A and 0 in column B when column B has more blank cells at the bottom than column A?

As previously requested, could you please upload a sample workbook

[Alan L 185]

Fantastic. It worked on my practice example. Thanks Tony.

[Tony Valko]

On the very top post of the thread, click the "Reply With Quote" button and you'll see the true data layout.

[ajryan88]

Ah I see...and here I was thinking I was an idiot for not understanding how your formula got the right answers!!! Because it certainly didn't when I tested it, but it does now that I have seen the proper layout of the data.

Thanks for the tip Tony

[Tony Valko]

It's weird how the forum strips out some extra space characters in certain displays yet allows them in others.

[Alan L 185]

Attached is a simple worked example with the formula that you've provided. Thanks.Worked Example - Count Blank Cells since last entry.xlsx

[Tony Valko]

You're welcome. Thanks for the feedback!

[Alan L 185]

Attached is a simple worked example with the formula that you've provided. Thanks.

[mlcb]

On the very top post of the thread, click the "Reply With Quote" button and you'll see the true data layout.

I also liked it!