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

Beware Wet Paint · Aug 2, 2013

mikerickson · Aug 2, 2013

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 · Aug 2, 2013

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 · Aug 2, 2013

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 · Aug 2, 2013

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 · Aug 3, 2013

Beware Wet Paint said:
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!

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

Beware Wet Paint

New Member

mikerickson

MrExcel MVP

Wessie

Board Regular

Beware Wet Paint

New Member

jackhandey

Board Regular

Wessie

Board Regular

Similar threads

Share this page

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

Beware Wet Paint

New Member

mikerickson

MrExcel MVP

Wessie

Board Regular

Beware Wet Paint

New Member

jackhandey

Board Regular

Wessie

Board Regular

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock