Help selecting and applying the correct function to return desired results please.

[Beware Wet Paint]

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; }.xl64 { text-align: center; }</style>

	A	B	C	D	E	F	G
1	Name	Checkouts	Kiosk	Self-Scan	CSD	Trolleys	Number of skills
2	Simon	Trained	Trained	Trained	Not trained	Not trained	3
3	Heather	Trained	Trained	Trained	Trained	Not trained	4
4	Emma	Trained	Not trained	Not trained	Not trained	Not trained	1
5	Jasmine	Trained	Trained	Trained	Not trained	Not trained	3
6	Hannah	Trained	Trained	Trained	Trained	Not trained	4
7	Sharon	Trained	Trained	Trained	Not trained	Not trained	3
8	Susan	Trained	Trained	Trained	Trained	Not trained	4
9	James	Trained	Not trained	Not trained	Not trained	Not trained	1
10	Emily	Trained	Not trained	Not trained	Not trained	Not trained	1
11	Paul	Trained	Not trained	Not trained	Not trained	Not trained	1
12	Sharon	Trained	Not trained	Not trained	Not trained	Not trained	1
13	Amy	Trained	Not trained	Not trained	Not trained	Not trained	1
14	Franny	Trained	Not trained	Trained	Not trained	Not trained	2
15	Patrick	Trained	Underage	Underage	Underage	Trained	2
16	Zoe	Trained	Underage	Underage	Underage	Not trained	1
17	Anthony	Not trained	Not trained	Trained	Not trained	Not trained	1
18	Val	Trained	Not trained	Not trained	Not trained	Not trained	1
19	Lousie	Trained	Not trained	Trained	Not trained	Not trained	2
20	Natalie	Trained	Trained	Trained	Not trained	Not trained	3
21	Peter	Trained	Trained	Trained	Not trained	Not trained	3
22	Shirley	Trained	Not trained	Not trained	Not trained	Not trained	1
23	Karen	Trained	Not trained	Trained	Not trained	Not trained	2
24	Nick	Not trained	Not trained	Not trained	Not trained	Trained	1
25	Lee	Trained	Trained	Trained	Not trained	Not trained	3
26	Joshua	Trained	Underage	Underage	Underage	Trained	2
27	Ayesha	Trained	Trained	Trained	Trained	Not trained	4
28	Claire	Trained	Not trained	Trained	Not trained	Not trained	2
29	Betty	Trained	Not trained	Trained	Not trained	Not trained	2
30	Adam	Trained	Trained	Trained	Not trained	Not trained	3
31	Julie	Trained	Not trained	Trained	Not trained	Not trained	2
32	Martin	Trained	Not trained	Not trained	Not trained	Not trained	1
33	Hilary	Trained	Underage	Underage	Underage	Not trained	1
34	Jack	Trained	Not trained	Trained	Not trained	Trained	3
35	Laura	Trained	Trained	Trained	Not trained	Not trained	3
36	Anna	Trained	Underage	Underage	Underage	Not trained	1
37	Joe	Trained	Not trained	Trained	Not trained	Trained	3
38	Elle	Trained	Not trained	Trained	Not trained	Not trained	2
39	Jane	Trained	Not trained	Not trained	Not trained	Not trained	1
40	Chris	Trained	Not trained	Trained	Not trained	Trained	3
41	Maria	Trained	Not trained	Trained	Not trained	Not trained	2
42	Rory	Trained	Underage	Underage	Not trained	Trained	2
43	Jamie	Trained	Not trained	Trained	Not trained	Trained	3
44	Victoria	Trained	Not trained	Trained	Not trained	Not trained	2
45	Christian	Trained	Trained	Trained	Not trained	Trained	4
46	Total no of colleagues trained	42	13	27	4	9

<colgroup><col style="mso-width-source:userset;mso-width-alt:7509;width:176pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="67"> <col style="width:65pt" span="2" width="65"> <col style="mso-width-source:userset;mso-width-alt:3669;width:86pt" width="86"> </colgroup><tbody>
</tbody>

Hello, I was wondering if anyone would possibly be able to help me.

Above is a spreadsheet I created of colleagues explaining which skills they are trained in: the text in each cell which intersects a name with a skill is selected from a drop-down menu of validated criteria: 'trained, not trained, underage'.

In column G I have calculated the total number of skills each colleague is trained in by using the formula =COUNTIF(B2:F2,"Trained") etc.

In row 46 I have calculated the total number of colleagues trained in a skill by using the formula =COUNTIF(B2:B45,"Trained") etc.

I was wondering if there would be a way to use a formula to return a list of names of colleagues who are trained in various combinations of skills? E.g a formula which would produce a list of names of colleagues who are trained on both checkout and self-scan etc? I'm uncertain as to which function/functions I'd have to use to achieve this or if this is even possible?

Any help would be brilliant and I'm a relative newbie I'm afraid.
Thank you

 

[mikerickson]

How about a formula like

=SUMPRODUCT( (INDEX(A1:G100,,MATCH("checkout",A1:G1,0))="trained") * (INDEX(A1:G100,,MATCH("self-serve",A1:G1,0))="trained") )

 

[Wessie]

Hey

Your best bet is using a Pivot table.

Click on any cell in your data
Go Insert > Pivot table and click OK
Select the "Name" column and drag it to Row labels
Column B-F you can pull to Report Filter (Or with Office 2010 you can go Insert Slicers)
Now you can select "Trained" in "Checkouts", "Kiosk" and "Trolleys" for instance and only "Christian" will be in your list

Viola!

 

[Beware Wet Paint]

Thank you mikerickson, that formula will really help with the future statistical comparisons I want to make
Thank you Wessie, that's hit the nail on the head for what I wanted to achieve- I was a bit scared of using pivot tables but you explained it in a very accessible manner

Much gratitude- as ever very prompt and helpful.

 

[jackhandey]

I think the easiest and fastest method here would be a quick pivot table. Highlight A1:G45, insert pivot, filter by all 5 training topics, and use Name for row labels. Through the filters, you can select "Trained" for each category you care about and the result will be a simple list of names of everyone who is trained on the topics you select.

If you're certain you want to use formulas, it will take a little bit more work. Here's one way to make it work (there are several, but this is my style I guess):

Insert a column to the left of your range. This will become a variable indexing column.

Off to the right of your range, perhaps in cells J2 through J6, set up 5 drop-down lists. Each list is data validated with the 5 training topics. The user could select which topics require trained colleagues.

Back to the inserted column: In what is now A2, enter this formula: =IF(AND(IFERROR(HLOOKUP(J$2,C$1:G2,ROW(),FALSE),"Trained")="Trained",IFERROR(HLOOKUP(J$3,C$1:G2,ROW(),FALSE),"Trained")="Trained",IFERROR(HLOOKUP(J$4,C$1:G2,ROW(),FALSE),"Trained")="Trained",IFERROR(HLOOKUP(J$5,C$1:G2,ROW(),FALSE),"Trained")="Trained",IFERROR(HLOOKUP(J$6,C$1:G2,ROW(),FALSE),"Trained")="Trained"),MAX(A$1:A1)+1,"")

Copy that down to A45. That will create an index for vlookup that changes based on what you require training in. It assumes you put your critera into J2 through J6.

Then create a numbered list of 1 through 44, perhaps starting in L2. Then, in M2, use this vlookup: =VLOOKUP(L2,A$2:B$45,2,FALSE)

It's quick, dirty, and gets the job done. If you need the output to look pretty, you can do a lot of things with formatting - but if this is for your own personal use, all you need is for it to work

 

[Wessie]

Thank you mikerickson, that formula will really help with the future statistical comparisons I want to make
Thank you Wessie, that's hit the nail on the head for what I wanted to achieve- I was a bit scared of using pivot tables but you explained it in a very accessible manner

Much gratitude- as ever very prompt and helpful.

Always glad to help!

Enjoy using Pivots in future!