Forum Discussion
Array function
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 | |
| 1 | Salesperson | Car Type | Number Sold | Unit Price |
| 2 | Barnhill | Sedan | 5 | 2200 |
| 3 | Barnhill | Coupe | 4 | 1800 |
| 4 | Ingle | Sedan | 6 | 2300 |
| 5 | Ingle | Coupe | 8 | 1700 |
| 6 | Jordan | Sedan | 3 | 2000 |
| 7 | Jordan | Coupe | 5 | 1600 |
| 8 | Pica | Sedan | 9 | 2150 |
| 9 | Pica | Coupe | 5 | 1950 |
| 10 | Sanchez | Sedan | 6 | 2250 |
| 11 | Sanchez | Coupe | 8 | 2000 |
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
Here are some variations on the formula given you by HansVogelaar , 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
- Thanks works great
