Hello
I have a table with duplicate values, I would like to get only the unique values, and then count those values by USEING ONLY ONE ARRAY_FURMULA.
Improvement: Multiple sorting of duplicated values in ascending order.
I have a table with duplicate values, I would like to get only the unique values, and then count those values by USEING ONLY ONE ARRAY_FURMULA.
Improvement: Multiple sorting of duplicated values in ascending order.
| Book1.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 2 | |||||||||
| 3 | DATA | Name | Type | # | |||||
| 4 | Name | Type | A | 5 | 1 | ||||
| 5 | A | 5 | A | 1 | 1 | ||||
| 6 | A | 1 | A | 2 | 1 | ||||
| 7 | B | 1 | B | 1 | 1 | ||||
| 8 | F | 2 | B | 2 | 1 | ||||
| 9 | B | 2 | C | 3 | 2 | ||||
| 10 | A | 2 | D | 3 | 2 | ||||
| 11 | C | 3 | F | 2 | 1 | ||||
| 12 | D | 3 | |||||||
| 13 | C | 3 | |||||||
| 14 | D | 3 | |||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | Merge all highlighted formulas in one (F4) | ||||||||
| 18 | Make Multi Sorting (Two levels) the name then the Type with function (SORT) | ||||||||
TEST | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F4:G11 | F4 | =SORT(UNIQUE(Table2)) |
| H4:H11 | H4 | =COUNTIFS(Table2[Name],F4,Table2[Type],G4) |
| Dynamic array formulas. | ||
