Return selected staff with count

[tigerzen]

I need a report that details the available staff and counts the number of shifts they are available for. The report should only list the available staff as per the example. Looking for a function based solution.

Lookups Match.xlsx
	A	B	C	D	E	F	G	H
1	Staff		Available Shifts				Desired Report
2	Peter		Peter	Ken	Peter		Peter	2
3	Harry		Ken	Mary	Tom		Ken	2
4	Fred		Mary				Mary	2
5	Mary						Tom	1
6	Ken
7	Tom
Sheet13

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Staff		Available Shifts				Desired Report
2	Peter		Peter	Ken	Peter		Peter	2
3	Harry		Ken	Mary	Tom		Ken	2
4	Fred		Mary				Mary	2
5	Mary						Tom	1
6	Ken
7	Tom
Data

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(TOCOL(C2:E7,1))
H2:H5	H2	=COUNTIFS(C2:E7,G2#)
Dynamic array formulas.

 

[tigerzen]

Thanks Fluff for the quick response, I was just about to edit my post and ask for an Excel 2019 solution as the person seeking the solution does not have 365.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Staff		Available Shifts				Desired Report
2	Peter		Peter	Ken	Peter		Peter	2
3	Harry		Ken	Mary	Tom		Mary	2
4	Fred		Mary				Ken	2
5	Mary						Tom	1
6	Ken
7	Tom
Data

Cell Formulas
Range	Formula
G2:G7	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$7)/(COUNTIFS($C$2:$E$7,$A$2:$A$7)>0),ROWS(G$2:G2))),"")
H2:H7	H2	=IF(G2="","",COUNTIFS($C$2:$E$7,G2))

 

[tigerzen]

Yep, that's fine, thanks for the lightning response.

 

[Fluff]

You're welcome & thanks for the feedback.