Countifs and Averages using multiple conditions

[Jazz Jones]

Good morning!

I might just need a coffee, but...

I have a data set that I need to calculate averages in.
In Column A, I have Function, in Column H I have Status, in Column S I have a calculated field showing No. of Weeks Active.

I need to pull out info using the following example:

Countif
Column A = 'Chief Operating Officer', and Column H = 'Future Joiner' OR 'Starter Commenced' (it should be noted that there is one other Status option in Column H, which is 'Active' - not sure if it might be easier to count and exclude active, rather than tot up the other two options).


I also need to pull:
If Column A = 'Chief Operating Officer', and Column H = 'Future Joiner' OR 'Starter Commenced', calculate the average of weeks active



I KNOW there's a simple solution to this, but it's early(ish) in the morning, and it's driving me barmy.

Thanks for your help, community!

Jasmine

 

[Comfy]

Is this what you are after?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Function							Status											No.Weeks Active
2	Chief Operating Officer							Future Joiner											2			Count	2
3	Chief Operating Officer							Starter Commenced											3			Avg Active	2.5
4	Chief Operating Officers							Future Joiner											3
Sheet1

Cell Formulas
Range	Formula
W2	=SUMPRODUCT(--($A$2:$A$4="Chief Operating Officer"),($H$2:$H$4="Future Joiner")+($H$2:$H$4="Starter Commenced"))
W3	=SUMPRODUCT($S$2:$S$4,--($A$2:$A$4="Chief Operating Officer")*(($H$2:$H$4="Future Joiner")+($H$2:$H$4="Starter Commenced")))/W2

 

[Jazz Jones]

This is great. Thank you so much. You saved my hide. Have a great day!