How do I create a list of names based on specific criteria?

Question

I've been looking at numbers far too long today. How do I create a list of all sales people that have sold 7 apples?

Who sold 7 apples?		Name of salesperson	Apples sold
		Johnny	7
		Molly	3
		Kim	7
		Michael	4
		Daniel	6
		Trina	7

Answer 1

Hi Charles,

Thank you for reaching out. My name is Furkaan, a user just like you. I’d be happy to help you with your concerns. There are a couple of ways to tackle this in Excel! Here are two options that work for different versions of Excel:

Option 1: Using Advanced Filter (Works in all Excel versions)

Copy the table header ("Who sold 7 apples?", "Name of salesperson", "Apples sold") and paste it below your data, leaving a blank row in between. This will be your output area for the filtered list.

Highlight your entire data table (including the headers).

Go to the Data tab and click Advanced.

In the Criteria Range box, select the row where you just pasted the table headers (one row below your original headers).

In the Copy to box, enter the cell address where you want your filtered list to start (the cell below the pasted headers you just copied).

Check the box next to "My data has headers".

Click the Copy entire rows option.

In the first row (Who sold 7 apples?), under Criteria, enter 7.

Click OK.

This will copy all rows where the value in the "Apples sold" column matches your criteria (7) into your new location.

Option 2: Using FILTER function (Available in Excel 365 and Excel 2019+)

In an empty cell below your data table, enter the formula: =FILTER(A2:C8, C2:C8 = 7, "")

(Replace A2:C8 with the actual range of your data table)

This formula uses the FILTER function to look at your data range (A2:C8) and return only the rows where the value in the "Apples sold" column (C2:C8) equals 7. The last argument ("" in this case) is for additional criteria you can add later if needed.

If you have any additional questions or concerns, please don’t hesitate to reach out. Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below. Best Regards, Furkaan

Answer 2

Hi,

In cell A2, enter this formula

=FILTER(C2:C7,D2:D7=7)

Hope this helps.

Answer 3

Thanks Ashish