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:

Name (Name is irrelevant)	Title
Doe, John	Mr.
Doe, John	PhD
Doe, John	MD
Doe, John	DO
Doe, John	PhD

<tbody>
</tbody>

Defined Name: "_OFF"

PhD

MD

DO

<tbody>
</tbody>

 

[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