Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Array function

Copper Contributor

I have a data set with multiple columns and I want to extract certain data into an array. See this example.

 

          A        B              C        D
1SalespersonCar TypeNumber SoldUnit Price
2BarnhillSedan52200
3BarnhillCoupe41800
4Ingle

Sedan

62300
5IngleCoupe81700
6JordanSedan32000
7JordanCoupe51600
8PicaSedan92150
9PicaCoupe51950
10SanchezSedan62250
11SanchezCoupe82000

I want to extract an list showing which salesperson sold 5 units so the results would appear like this.

Barnhill
Jordan
Pica

What combination of formulas will do this?

4 Replies

@unclepauly28 

 

=FILTER(A2:A11, C2:C11=5, "")

@unclepauly28 

 

Here are some variations on the formula given you  by @Hans Vogelaar , to give an idea of how slightly different questions could be asked of the same data. See the attached file for these examples.

 

Who sold exactly five units?

=FILTER(A2:A11,C2:C11=5,"")

Who sold five or more units? The UNIQUE function is added here because some names would appear twice; Sanchez, for example, sold 6 sedans and 8 coupes.

=UNIQUE(FILTER(A2:A11,C2:C11>=5,""))

Who sold five or more Sedans?

=FILTER(A2:A11,(C2:C11>=5)*(B2:B11="Sedan"),"")

Who sold five or more Coupes?

=FILTER(A2:A11,(C2:C11>=5)*(B2:B11="Coupe"),"")

   

There are more variations on these formulas, depending on what the question is.

Thanks works great
Thanks