I have a sheet with several series separated by an empty row listed down in rows, I'd like to know the # rows in each series and place the count ("X rows" - see below) somewhere, e.g. next to the first row in each series. Is there an Excel formula that could do this or would this require VBA coding (that is easy, I know).
AA 3 rows
BB
CC
AAA 2 rows
BBB
AAAA 4 rows
BBB
CCCC
D
...
Counting series
-
ErikJan
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
HansV
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Counting series
The following will only work if there is a blank cell above the first one with data (the "AA" in your example); otherwise the formula will become slightly more complicated.
Let's say the data ("AA", "BB" etc.) start in cell A2.
In B2, enter the following array formula (i.e. confirm with Ctrl+Shift+Enter):
=IF(A1="",MIN(IF(A2:A$1000="",ROW(A2:A$1000)))-ROW()&" rows","")
Adjust both occurrences of 1000 to a number larger than the highest row number you expect to use.
Fill down from B2 as far as needed.
Sample workbook attached:
Let's say the data ("AA", "BB" etc.) start in cell A2.
In B2, enter the following array formula (i.e. confirm with Ctrl+Shift+Enter):
=IF(A1="",MIN(IF(A2:A$1000="",ROW(A2:A$1000)))-ROW()&" rows","")
Adjust both occurrences of 1000 to a number larger than the highest row number you expect to use.
Fill down from B2 as far as needed.
Sample workbook attached:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
ErikJan
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Counting series
Brilliant! Thanks (again) ;-)