Combining IF, COUNTIF, ISNUMBER, SEARCH in a formula

[MarcusEd]

Hi

Hoping someone might be able to point me towards a solution that has me stumped combining a number of search functions.

I have a roster with three general information columns: Position title, is the position active, company the position reports to. There is a separate summary sheet that is designed to summarise (by count) the number of positions budgeted versus the number of actual active positions filled.

I’m trying to do a count of position titles but also need to include them in the count only if the position is active and whether it works for a particular company. I’ve tried using the COUNTIF, IF(AND and IF(ISNUMBER(SEARCH but am having trouble bringing them all together with a single output result.

For example: to find the number of cells in a range that have the word “Manager” in the title (in one column) then reference whether this position is active (as designed by the word “Active”) in a second column then search a third column whether this position works for “Company A”

 

[teylyn]

Add another column called "Role" to your data that specifies if a person is Manager, Supervisor, etc. Then simply build a pivot table. Use Roles and company in the rows and Active in the columns. Drag the title into the value area for a count.

 

[JoeMo]

Book1
	A	B	C	D	E
1	Title	Active	Company
2	Manager A	Active	Company A
3	Manager B	Active	Company A
4	Supervisor A	Active	Company A
5	Supervisor B	Active	Company A
6	Supervisor C	Not active	Company A
7	Trainer	Active	Company A
8	Engineer A	Not active	Company A
9	Engineer B	Active	Company A
10	Engineer C	Active	Company A
11	Engineer D	Not active	Company A
12	Engineer E	Active	Company A
13
14	2	# Active Managers from Comppany A
15	2	# Active Supervisors from Comppany A
16	1	# Active Trainers from Comppany A
17	3	# Active Engineers from Comppany A
Sheet1

Cell Formulas
Range	Formula
A14	A14	=COUNTIFS($A$2:$A$12,"*Manager*",$B$2:$B$12,"Active",$C$2:$C$12,"Company A")
A15	A15	=COUNTIFS($A$2:$A$12,"*Supervisor*",$B$2:$B$12,"Active",$C$2:$C$12,"Company A")
A16	A16	=COUNTIFS($A$2:$A$12,"*Trainer*",$B$2:$B$12,"Active",$C$2:$C$12,"Company A")
A17	A17	=COUNTIFS($A$2:$A$12,"*Engineer*",$B$2:$B$12,"Active",$C$2:$C$12,"Company A")

 

[AhoyNC]

Try COUNTIFS:

Book1
	A	B	C	D	E	F	G	H
1	Title	Active	Company		Title	Count	Active	Com. A
2	Manager A	Active	Com. A		Manager	2
3	Manager B	Active	Com. A		Supervisor	2
4	Manager C	Active	Com. C		Eng	3
5	Supervisor A	Active	Com. A
6	Supervisor B	Active	Com. A
7	Supervisor C	Not Active	Com. A
8	Eng A	Not Active	Com. A
9	Eng B	Active	Com. A
10	Eng C	Active	Com. A
11	Eng D	Not Active	Com. A
12	Eng E	Active	Com. A
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS($A$2:$A$12,"*"&$E2&"*",$B$2:$B$12,$G$1,$C$2:$C$12,$H$1)

 

[MarcusEd]

Thank you all for your replies. The COUNTIFS function is ideal and achieves what i'm after.
I thought about a Pivot table but ruled it out for a couple of administrative reasons