Listing items from a range if a criteria is met

[jwgreen1986]

i have a payroll tab which lists all member of staff for a department. I want a formula that will look at this whole list and then list the users employment id's when a criteria is met. so in total i want to produce 3 seperates.

i want one that lists all the staff who are down as new sales.
i want one that lists all the staff who are down as used sales
i want one that lists all the staff who are down as combined sales.

i am only interested in it bring back the users ID, but it would be handy if it also brought back their name and location as well.

i understand basic index formulas but i am struggling to find a formula that lists people.
Below is a basic idea of what i have in my spreadsheet. i want to look up the value Used, New, and Combined in the pay scheme column and then list the relevant Employee ID's.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Employer ID[/TD]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Pay Scheme[/TD]
[/TR]
[TR]
[TD]Derby[/TD]
[TD]abc[/TD]
[TD]Bob[/TD]
[TD]Marley[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]Newcastle[/TD]
[TD]123[/TD]
[TD]Jack[/TD]
[TD]O'Lantern[/TD]
[TD]Used[/TD]
[/TR]
[TR]
[TD]Leicester[/TD]
[TD]def[/TD]
[TD]Emma[/TD]
[TD]Stone[/TD]
[TD]Combined[/TD]
[/TR]
[TR]
[TD]Manchester[/TD]
[TD]456[/TD]
[TD]Kurt[/TD]
[TD]Kliff[/TD]
[TD]Combined[/TD]
[/TR]
[TR]
[TD]Burton[/TD]
[TD]ghi[/TD]
[TD]Kyle[/TD]
[TD]Boy[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]789[/TD]
[TD]Louise[/TD]
[TD]Under[/TD]
[TD]Used[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

try

Book1
	A	B	C	D	E
1	Location	Employer ID	First Name	Surname	Pay Scheme
2	Derby	abc	Bob	Marley	New
3	Newcastle	123	Jack	O'Lantern	Used
4	Leicester	def	Emma	Stone	Combined
5	Manchester	456	Kurt	Kliff	Combined
6	Burton	ghi	Kyle	Boy	New
7	Liverpool	789	Louise	Under	Used
8
9	new
10	abc	Bob	Marley	Derby
11	ghi	Kyle	Boy	Burton
12
13
14
15	Used
16	123	Jack	O'Lantern	Newcastle
17	789	Louise	Under	Liverpool
18
19
20	Combined
21	def	Emma	Stone	Leicester
22	456	Kurt	Kliff	Manchester
23
Sheet1

Cell Formulas
Range	Formula
A10	{=IF(ROWS(A$10:A10)>COUNTIF($E$2:$E$7,"new"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$10:A10))))}
A11	{=IF(ROWS(A$10:A11)>COUNTIF($E$2:$E$7,"new"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$10:A11))))}
A12	{=IF(ROWS(A$10:A12)>COUNTIF($E$2:$E$7,"new"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$10:A12))))}
A13	{=IF(ROWS(A$10:A13)>COUNTIF($E$2:$E$7,"new"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$10:A13))))}
A16	{=IF(ROWS(A$16:A16)>COUNTIF($E$2:$E$7,"used"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$16:A16))))}
A17	{=IF(ROWS(A$16:A17)>COUNTIF($E$2:$E$7,"used"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$16:A17))))}
A18	{=IF(ROWS(A$16:A18)>COUNTIF($E$2:$E$7,"used"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$16:A18))))}
A21	{=IF(ROWS(A$21:A21)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$21:A21))))}
A22	{=IF(ROWS(A$21:A22)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$21:A22))))}
A23	{=IF(ROWS(A$21:A23)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($B$2:$B$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$21:A23))))}
B10	{=IF(ROWS(B$10:B10)>COUNTIF($E$2:$E$7,"new"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$10:B10))))}
B11	{=IF(ROWS(B$10:B11)>COUNTIF($E$2:$E$7,"new"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$10:B11))))}
B12	{=IF(ROWS(B$10:B12)>COUNTIF($E$2:$E$7,"new"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$10:B12))))}
B13	{=IF(ROWS(B$10:B13)>COUNTIF($E$2:$E$7,"new"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$10:B13))))}
B16	{=IF(ROWS(B$16:B16)>COUNTIF($E$2:$E$7,"used"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$16:B16))))}
B17	{=IF(ROWS(B$16:B17)>COUNTIF($E$2:$E$7,"used"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$16:B17))))}
B18	{=IF(ROWS(B$16:B18)>COUNTIF($E$2:$E$7,"used"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$16:B18))))}
B21	{=IF(ROWS(B$21:B21)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$21:B21))))}
B22	{=IF(ROWS(B$21:B22)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$21:B22))))}
B23	{=IF(ROWS(B$21:B23)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($C$2:$C$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(B$21:B23))))}
C10	{=IF(ROWS(C$10:C10)>COUNTIF($E$2:$E$7,"new"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$10:C10))))}
C11	{=IF(ROWS(C$10:C11)>COUNTIF($E$2:$E$7,"new"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$10:C11))))}
C12	{=IF(ROWS(C$10:C12)>COUNTIF($E$2:$E$7,"new"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$10:C12))))}
C13	{=IF(ROWS(C$10:C13)>COUNTIF($E$2:$E$7,"new"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$10:C13))))}
C16	{=IF(ROWS(C$16:C16)>COUNTIF($E$2:$E$7,"used"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$16:C16))))}
C17	{=IF(ROWS(C$16:C17)>COUNTIF($E$2:$E$7,"used"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$16:C17))))}
C18	{=IF(ROWS(C$16:C18)>COUNTIF($E$2:$E$7,"used"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$16:C18))))}
C21	{=IF(ROWS(C$21:C21)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$21:C21))))}
C22	{=IF(ROWS(C$21:C22)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$21:C22))))}
C23	{=IF(ROWS(C$21:C23)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($D$2:$D$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(C$21:C23))))}
D10	{=IF(ROWS(D$10:D10)>COUNTIF($E$2:$E$7,"new"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$10:D10))))}
D11	{=IF(ROWS(D$10:D11)>COUNTIF($E$2:$E$7,"new"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$10:D11))))}
D12	{=IF(ROWS(D$10:D12)>COUNTIF($E$2:$E$7,"new"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$10:D12))))}
D13	{=IF(ROWS(D$10:D13)>COUNTIF($E$2:$E$7,"new"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="new",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$10:D13))))}
D16	{=IF(ROWS(D$16:D16)>COUNTIF($E$2:$E$7,"used"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$16:D16))))}
D17	{=IF(ROWS(D$16:D17)>COUNTIF($E$2:$E$7,"used"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$16:D17))))}
D18	{=IF(ROWS(D$16:D18)>COUNTIF($E$2:$E$7,"used"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="used",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$16:D18))))}
D21	{=IF(ROWS(D$21:D21)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$21:D21))))}
D22	{=IF(ROWS(D$21:D22)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$21:D22))))}
D23	{=IF(ROWS(D$21:D23)>COUNTIF($E$2:$E$7,"combined"),"",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7="combined",ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(D$21:D23))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tyija1995]

You could use a pivot table to get what you want as well, just bang all of the fields in to Rows and show in tabular form, take off grand totals & subtotals and this will create what you are looking for. You can then use pivot slicers etc to filter on criteria for easier navigation.

 

[jwgreen1986]

Brilliant, this is just want i wanted. slight addition to this. say i wanted to create a list that contained the new and Combined, how would i incorporate this to the formula. obviously i understand the $E$2:$E$7,"new" of the formula, but how do i change it so it says $E$2:$E$7,"new" or $e$2:$e$7,"Combined"

 

[jwgreen1986]

im needing something more permanent than pivots. i am looking to create a league table based on the info. the first part is splitting people to the new, used, combined lists. once this is done i can then start pulling in the relevant info to form a league table.

 

[Scott T]

This should work

=IF(ROWS(A$10:A10)>(COUNTIF($E$2:$E$7,"new")+COUNTIF($E$2:$E$7,"combined")),"",INDEX($B$2:$B$7,AGGREGATE(15,6,1/(($E$2:$E$7="new")+($E$2:$E$7="combined"))*(ROW($E$2:$E$7)-ROW($E$2)+1),ROWS(A$10:A10))))

 

[jwgreen1986]

brilliant this is now working. any suggestions for the below. trying to get it to bring back the total sales depending on the dates i put in the formula. so the example below i want it to bring back the total sales in january. if i take out the date ranges from the formula then it works but it brings back the overall total for that person. i want to be able to view their total sales per month.

=IF(K4="","",IF(OR(K4="Used",K4="Combined"),COUNTIFS(Sales!Q:Q,"YES",Sales!S:S,H4,Sales!R:R,"New",Sales!H:H,">=01/01/2019",Sales!H:H,"<=31/01/2019")))