I download an excel file of survey answers on a monthly basis for a report.

Each line item is an unique entry.

For a particular questions (multiple select): example: Which fruits do you eat: A / B / C

It puts all the answers into 1 cell separated by commas. Example "A, C" or "A" or "A, B, C".

When I pivot and sum the frequency, it shows the it based on: "A, C" or "A" or "A, B, C".

Question: How can i sum the frequency based on the individual option:

A: 7

B: 11

C: 11

From:

Fields | frequency |

A | 1 |

A, B | 2 |

A, B, C | 3 |

A, C | 1 |

B | 5 |

B, C | 6 |

C | 1 |

<TBODY>

</TBODY>

Caveat: there are more than 3 types of fields (A, B and C), they are text, some quite long.

Thank you for your time, i hope i was clear.

Cheers,

Dean