Forum Discussion
need a formula to group
| COLUMN1 | COLUMN2 |
| D | PU-6 |
| B | PU-3 |
| D | PU-3 |
| D | PU-7, PU-6 |
| B | PU-2, PU-3, PU-3 |
| B | PU-2, PU-3, PU-2 |
| B | PU-6, PU-7, PU-7 |
| B | PU-1, PU-3, PU-4, PU-5, PU-7, PU-4 |
so there are 2 groups group B and group D each has values ranging from PU-1 to PU-8 i want them to count (Frequency of each) in following format.
| D | B | |
| PU-1 | ||
| PU-2 | ||
| PU-3 | ||
| PU-4 | ||
| PU-5 | ||
| PU-6 | ||
| PU-7 | ||
| PU-8 |
1 Reply
=SUMPRODUCT((E$1=$A$2:$A$9)*(LEN($B$2:$B$9)-LEN(SUBSTITUTE($B$2:$B$9,$D2,"")))/LEN($D2))You can try this formula.
