Counting number of items in column since last blank

Hi all,

Is there a way to count the number of occurrences of text in a column, but only counting text since the last blank line?

Example:

Text
Text
Text
Text
[blank]
Text
Text
Text
Text
Text
Text
Text
Text


I would want it to give me the number 8, which is the count of all the items since the last blank.
Can you help? My boss has asked me for this and I'm still learning Excel, so any input would be greatly appreciated! Thank you!

There will be multiple blanks. It's a column of 366 lines, each line corresponds to a date. In this particular column, we write the issue occurring on that date, and once the issue is resolved, it's no longer listed for the following days. My boss wants me to count the number of days that there has been an issue. So if the line is blank, that means no issue; once it has text in it, that means it's day 1 of the issue, etc. I hope this makes sense!

Lets use the following example:

If I had this Column A (row / text):

01 / A
02 / A
03 / A
04 / A
05 / A
06 / A
07 / A
08 / A
09 / A
10 /
11 / A
12 / A
13 / A
14 /
15 / A
16 / A
17 / A
18 / A
19 / A
20 /
21 / A
22 / A
23 / A
24 /
25 / A
26 / A
27 / A

The empty spots are the blank cells

Put this formula where you want to count:

Please Login or Register  to view this content.

Don't forget to CTRL + SHIFT + Enter after... cause it is a matrix

If this was taking place in column U rows 2-367, would I replace A1:A27 with U2:U367, etc for the whole thing?

Another solution with Frequency:
copy to clipboard
=INDEX(FREQUENCY(IF($A$2:$A$11<>"",ROW($A$2:$A$11),""),IF($A$2:$A$11="",ROW($A$2:$A$11),"")),COUNTBLANK($A$2:$A$11)+1)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I just tried this and it's giving me 0, even though I put text in the column as a test

You replace the OFFSET($A$1 for OFFSET($U$2) and the range $A$1:$A$27 for $U$2:$U$367

Originally Posted by bebo021999

Have you confirmed by hitting Ctrl-shift-enter?

Yes, and the brackets showed up and everything.

This looks like it's exactly what I need! I am trying to upload the book I'm working in but it's not loading. Here's a twist - can we make it so that if Column A reads today's date, and the text of the column question is blank on the same row as today's date, the cell with our formula shows 0? or better yet, blank?

Originally Posted by ChemistB

You lost me on that last question. If your workbook is too large it won't load, trim down your data.

11.9 KB and it's still not loading Today is just not my day!

Originally Posted by ChemistB

You lost me on that last question. If your workbook is too large it won't load, trim down your data.

Basically I need this whole formula to be conditional on the fact that today's date is found in column that lists all 366 days of the year. Say today's date is in row 10. If row 10 doesn't have text in it in the column that I'm trying to count (not the date column, the column where I'm trying to count occurrences of text), then the formula doesn't have to count the last occurrences of text and can read 0. I really wish I could upload the workbook to show you!