Dynamic Counting Formula

[beeehop]

I have this pivot table:

In column D, I'd like a formula that counts Employee Names in column C grouped by column B.

For example, in the picture above, Senior Consultant April Thomas will have the number 8 - a count of employees under her.

Is there a formula to count occupied cells in one column up until a value exists in another column?

Here's what I thought up so far and I am stuck:

 

[Cubist]

It should be easy if you have the Senior Consultant on every line, then you can just use the FILTER().

 

[Cubist]

Also, this looks like a Pivot Table, you can probably just FILTER() on the original data set.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you use something like this?

23 10 13.xlsm
	B	C	D
1
2
3
4	Consultant	Name	Count
5	a	Name 1	4
6		Name 2
7		Name 3
8		Name 4
9	b	Name 5	1
10	c	Name 6	7
11		Name 7
12		Name 8
13		Name 9
14		Name 10
15		Name 11
16		Name 12
17	d	Name 13	4
18		Name 14
19		Name 15
20		Name 16
21
Count

Cell Formulas
Range	Formula
D5:D20	D5	=IF(B5="","",COUNTA(C5:C$1000)-SUM(D6:D$1000))