Pulling in data based on certain criteria in another column

NikkiOno

Hello, I am trying to pull in all the employee names that work in sales from the Employee Database sheet to a new sheet but my formula is only pulling in one name. The sheet below is the sheet where all the data is.

=INDEX(COLLECT({employee name}, {department}, "sales"), 1)

Paul Newcome

Have you looked into creating a report instead?

To do this in a sheet, you would need to adjust the 1 at the end to a 2 for the second entry, 3 for the third, so on and so forth. You can use a helper column that has the numbers manually entered and do a cell reference instead as well.

NikkiOno

@Paul Newcome the report seems messy and hard to read because I'm adding daily sales and it shows a line for each sale per every sales associate. does that make sense?

can you explain more what you mean by adding a 2, 3, 4 at the end of the forumual? do i need to create a forumual for each row and each formula has a separate number? or one formula like this =INDEX(COLLECT({employee name}, {department}, "sales"), 1, 2, 3)

Paul Newcome

You would change the number at the end.

=INDEX(COLLECT({employee name}, {department}, "sales"), 1)

=INDEX(COLLECT({employee name}, {department}, "sales"), 2)

=INDEX(COLLECT({employee name}, {department}, "sales"), 3)

NikkiOno

@Paul Newcome Thank you! that worked.

another question for the same new sheet is that i need to sum all the sales for each person in the past 10 days. i tried this formula but its not working. =SUMIFS({net sales}, {sales associate}, [Sales Associate]@row, {date} >= TODAY(10), {date} <= TODAY())

NikkiOno

@Paul Newcome

I figured it out after three hours. =SUMIFS({net sales}, {date}, >=TODAY(-10), {date}, <=TODAY(), {sales associate}, [Sales Associate]@row)

Paul Newcome

@NikkiOno Happy to help, and glad you got that SUMIFS sorted. 👍️