# Sum frequency based on adjacent cell with separated by comma

#### DeanA1712

##### New Member
Hi Guys,

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

##### MrExcel MVP
Hi Guys,

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
 Fields frequency A 1 A 7 A, B 2 B 16 A, B, C 3 C 11 A, C 1 B 5 B, C 6 C 1

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;" width="100"> <col style="width: 48pt;" span="6" width="64"> <tbody>
</tbody>

G2, copied down:

=SUMIF(\$A\$2:\$A\$8,"*"&F2&"*",\$B\$2:\$B\$8)

#### DeanA1712

##### New Member
Hey Man, Thanks. That works perfectly.
So much time saved

##### MrExcel MVP
Hey Man, Thanks. That works perfectly.
So much time saved
You are welcome. Thanks for providing feedback.

1,081,536
Messages
5,359,372
Members
400,525
Latest member
swwber

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...