Counting Using Defined Names

[tycasey17]

I am wanting to count the number of personnel within a defined name. I have tried using =COUNTIF(A1:A5,_OFF) "_OFF" is the defined name having various titles in them. The overall outcome of the formula I am needing help with is to show there are 4 doctors of the entire range using the _OFF defined name. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name (Name is irrelevant)[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]Mr.[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]PhD[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]MD[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]DO[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]PhD[/TD]
[/TR]
</tbody>[/TABLE]

Defined Name: "_OFF"
[TABLE="width: 500"]
<tbody>[TR]
[TD]PhD
[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[/TR]
[TR]
[TD]DO[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Excel Workbook
	A	B	C	D	E
1	Name	Title		Title	Count
2	john	mr		phd	2
3	john	phd		md	2
4	john	Md		do	1
5	john	do
6	john	Md
7	john	phd
sheet1

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C	D	E
1	Name (Name is irrelevant)	Title		PhD
2	Doe, John	Mr.		MD
3	Doe, John	PhD		DO
4	Doe, John	MD			4
5	Doe, John	DO
6	Doe, John	PhD
Jan 2018

Cell Formulas
Range	Formula
E4	{=SUM(COUNTIF(B2:B6,_Off))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_Off	='Jan 2018'!$D$1:$D$3

 

[tycasey17]

Thank you.

 

[Fluff]

Glad we could help