How to get names from a list based on multiple criteria

Hi,

I am trying to find/workout a formula which can help me produce a list of names which are taken from a separate worksheet based on multiple and potentially different criteria. I have attached an example to help try and explain.

In the example I have a home page where I can select from 3 different sites and also input a date. This will then give me a total of sales carried out on that site on the given day. I would then like to be able to go to a different worksheet (sales breakdown) where I can see a list of the sales people who have sold vehicles on that day. The issue that I am having is getting this page to only show the results and names for the site that is selected on the main page. I also have the added confusion where some sales people will go to work at different sites, for example N.Smith was in Manchester on the 13/4 but usually works in London. In the breakdown I would only want to see his name if he was at the selected site on the selected day.

I have filled in the example sheet to show what I would like to see to try and make it easier to understand.

Thanks in advance

Dom

Names ..

in A2

=IFERROR(INDEX('Datbase Data'!$C$2:$C$9,SMALL(IF(('Datbase Data'!$D$2:$D$9=Sheet1!$F$6)*('Datbase Data'!$B$2:$B$9=Sheet1!$F$8),ROW($A$2:$A$9)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

Total

in B2

=COUNTIFS('Datbase Data'!$C$2:$C$9,'Sales Breakdown'!A2,'Datbase Data'!$D$2:$D$9,Sheet1!$F$6,'Datbase Data'!$B$2:$B$9,Sheet1!$F$8)

Copy both down

Hi John,

Thanks for your help, this has worked fine in my example book but I'm struggling to get it to work within my live workbook, I have amended the formula to the below so the worksheets match the names of what I have and the columns are also correct for where they need to look:

=IFERROR(INDEX('Main_Inspection Data'!$F:$F,SMALL(IF(('Main_Inspection Data'!$E:$E='Control Panel'!$F$11)*('Main_Inspection Data'!$H:$H>='Control Panel'!$F$16),ROW($AA$6:$AA$13)-ROW($AA$6)+1,""),ROWS($AA$6:AA6))),"")

The formula isn't returning any errors but is just leaving a blank cell. Can you see anything that I have changed that I shouldn't have done?

Also I understand most of this formula (I think!!) except for the "SMALL" part near the beginning, what is this asking the formula to do?

Thanks again for your help

Try

=IFERROR(INDEX('Main_Inspection Data'!$F6:$F13,SMALL(IF(('Main_Inspection Data'!$E6:$E13='Control Panel'!$F$11)*('Main_Inspection Data'!$H6:$H13>='Control Panel'!$F$16),ROW($AA$6:$AA$13)-ROW($AA$6)+1,""),ROWS($AA$6:AA6))),"")

Enter with Ctrl+Shift+Enter


Change ALL the 6:13 ranges to those in your worksheet.

Avoid generally using full columns i.e. E:E as this can impact performance: there some functions which handle full columns but array formula like the above tend to be resource hungry.

I have tried changing the formula as suggested but seem to still be getting the same results. I have also tried changing part of the formula to AA:AA (I know you said to try and avoid using full columns with an array but just been trying everything!!)

=IFERROR(INDEX('Main_Inspection Data'!$F:$F,SMALL(IF(('Main_Inspection Data'!$E:$E='Control Panel'!$F$11)*('Main_Inspection Data'!$H:$H>='Control Panel'!$F$16),ROW($AA:$AA)-ROW($AA$6)+1,""),ROWS($AA$6:AA6))),"")

If I put in the formula like this it is returning a name but it doesn't match the location or date and is also returning duplicate names

From the formula you posted, the "live" format appears to be the same as your sample so I cannot see why it does not work.

To repeat ...

Enter with Ctrl+Shift+Enter

You will see these brackets round the formula if entered correctly ...{ ..... }

And reset ranges as this will give wrong results

ROW($AA:$AA)-ROW($AA$6)+1

needs to be ......

ROW($AA$6:$AA$1000)-ROW($AA$6)+1

I have entered this way and also changed the ranges, the formula is going through but returning no results:

Capture1.PNG

I (we) need a file (subset of your live file?) to test against (not an image): and I can't view PNG images.