Sum frequency based on adjacent cell with separated by comma

DeanA1712

New Member
Joined
Jan 11, 2013
Messages
5
Hi Guys,

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:
Fieldsfrequency
A1
A, B2
A, B, C3
A, C1
B5
B, C6
C1

<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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Hi Guys,

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
Fieldsfrequency
A1 A7
A, B2 B16
A, B, C3 C11
A, C1
B5
B, C6
C1

<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)
 

Forum statistics

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

Some videos you may like

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...
Top