Summing the instances of a specific string by a corresponding column's number in a pivot table

daniel_san

New Member
Joined
Apr 6, 2017
Messages
8
Hi, I’m hoping one of you can help me. I have built a pivot table to display the results of a survey. One of the survey questions allows multiple answers, unfortunately the answers are in one string (see below). I am unable to change how I receive the results.

“Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance”

The pivot table obviously counts the number of occurrences for each response, but due to different strings of answers, it only counts where a string is identical. For example below, “Choice of accessories” appears 275 times whereas “Choice of accessories;Easy to use” appears 9 times etc.

[B]Excel 2010[/B][TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]What attracted you to this item? (Please select up to 3)[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Choice of accessories[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Choice of accessories;Easy to use[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance;Previous experience of Russell Hobbs;Quality;Reliability;Stylish;Special offer;Well designed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance;Russell Hobbs reputation;Special offer[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Size of product/space saving;Stylish;Well designed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Powerful performance;Product features;Quality;Russell Hobbs reputation;Safe;Stylish;Special offer;Well designed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Powerful performance;Product features;Safe;Size of product/space saving;Special offer;Well designed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Product features;Safe[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Lightweight;Powerful performance;Product features;Size of product/space saving;Stylish[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Quality;Reliability;Russell Hobbs reputation;Safe;Stylish;Special offer[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Lightweight[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Powerful performance;Previous experience of Russell Hobbs;Quality;Reliability;Russell Hobbs reputation;Safe;Stylish;Special offer;Well designed[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Quality;Russell Hobbs reputation;Stylish;Special offer[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet4[/B][/COLOR][/CENTER]



I’m only interested in counting the answers individually, e.g. “Choice of accessories” and “Easy to use” etc. So this would involve summing column B for each corresponding row a specific answer appears in a string. For example, the first three rows, “Choice of accessories” appears in each row, resulting in summing 275+9+13 .

To do this I have built a table on a separate worksheet which reads from the pivot table (see below).

[B]Excel 2010[/B][TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFC000"]What attracted you to this item? (can select up to 3)[/TD]
[TD]No. of Responses[/TD]
[TD]Percentage[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Not applicable - received as a gift[/TD]
[TD="align: right"] 146[/TD]
[TD="align: right"]0.3%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Product features[/TD]
[TD="align: right"] 1,825[/TD]
[TD="align: right"]3.4%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Choice of accessories[/TD]
[TD="align: right"] 376[/TD]
[TD="align: right"]0.7%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Quality[/TD]
[TD="align: right"] 2,316[/TD]
[TD="align: right"]4.4%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Ease of use[/TD]
[TD="align: right"] 2,241[/TD]
[TD="align: right"]4.2%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Reliability[/TD]
[TD="align: right"] 1,667[/TD]
[TD="align: right"]3.1%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Energy efficient and eco friendly features[/TD]
[TD="align: right"] 1,372[/TD]
[TD="align: right"]2.6%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Russell Hobbs reputation[/TD]
[TD="align: right"] 2,685[/TD]
[TD="align: right"]5.1%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Fast results/time saving[/TD]
[TD="align: right"] 1,512[/TD]
[TD="align: right"]2.8%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Safe[/TD]
[TD="align: right"] 966[/TD]
[TD="align: right"]1.8%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Length of guarantee[/TD]
[TD="align: right"] 1,753[/TD]
[TD="align: right"]3.3%[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Size of product/space saving[/TD]
[TD="align: right"] 921[/TD]
[TD="align: right"]1.7%[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Lightweight[/TD]
[TD="align: right"] 1,297[/TD]
[TD="align: right"]2.4%[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Stylish[/TD]
[TD="align: right"] 2,070[/TD]
[TD="align: right"]3.9%[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Powerful performance[/TD]
[TD="align: right"] 1,407[/TD]
[TD="align: right"]2.7%[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]Value for money/special offer[/TD]
[TD="align: right"] 2,393[/TD]
[TD="align: right"]4.5%[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]Previous experience of Russell Hobbs[/TD]
[TD="align: right"] 2,009[/TD]
[TD="align: right"]3.8%[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]Well designed[/TD]
[TD="align: right"] 2,085[/TD]
[TD="align: right"]3.9%[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]Total no. of people who responded[/TD]
[TD="align: right"] 53,093[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Sheet2[/B][/COLOR][/CENTER]



This table uses the below formula which strips out and counts every instance of specific answers in column A of the pivot table (it counts each row the answer appears in a string).

“=SUM(LEN(Sheet4!A:A)-LEN(SUBSTITUTE(Sheet4!A:A,"Not applicable - received as a gift","")))/LEN("Not applicable - received as a gift")”

However, this is only half of what I need. I also need the table to not only strip out every instance of a specific answer, e.g. “Choice of accessories” and count it, I then need it to sum the corresponding number in column B of the pivot table which is the number of instances that particular string appears in the data. Is this possible by adapting the existing formula or would it need an entirely new formula?

Any help any of you could provide would be massively appreciated as I’ve been scratching my head on this for the last two days. You’re my last resort!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
my answers seem different to yours

Not applicable - received as a giftProduct featuresChoice of accessoriesQualityEase of useReliabilityEnergy efficient and eco friendly featuresRussell Hobbs reputationFast results/time savingSafeLength of guaranteeSize of product/space savingLightweightStylishPowerful performanceValue for money/special offerPrevious experience of Russell HobbsWell designed
Choice of accessories001000000000000000
Choice of accessories;Easy to use001000000000000000
Choice of accessories;Easy to use;Energy efficient and eco friendly features0010003500000000000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight001000350780103012300000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance00100035078010301230135000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance;Previous experience of Russell Hobbs;Quality;Reliability;Stylish;Special offer;Well designed001193020135078010301232131350156235
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Powerful performance;Russell Hobbs reputation;Special offer0010003515678010301230135000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight;Size of product/space saving;Stylish;Well designed001000350780103135123164000172
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Powerful performance;Product features;Quality;Russell Hobbs reputation;Safe;Stylish;Special offer;Well designed014411610035169781941030019912300221
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Powerful performance;Product features;Safe;Size of product/space saving;Special offer;Well designed01441000350781611031660012300209
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Product features;Safe01231000350781401030000000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Lightweight;Powerful performance;Product features;Size of product/space saving;Stylish013610003507800153103182115000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Quality;Reliability;Russell Hobbs reputation;Safe;Stylish;Special offer001103011135123781480001530000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Lightweight001000350007809800000
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Powerful performance;Previous experience of Russell Hobbs;Quality;Reliability;Russell Hobbs reputation;Safe;Stylish;Special offer;Well designed00115601643517602017800206980119228
Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Quality;Russell Hobbs reputation;Stylish;Special offer0019800351060078001310000
05471671104764907307808441058454816124886402751065

<colgroup><col><col><col span="5"><col><col span="4"><col><col span="8"></colgroup><tbody>
</tbody>
 
Upvote 0
Sorry, I didn't include the full pivot table because there are over 5,000 rows of results. I'm working from data with over 53,000 responses and there are over 5,000 variations on how people answered the "what attracted you to this item?" question. The formula is only counting each row the specific answers appear. What I want it to do is sum the corresponding number in column B every time a specific answer appears in a string in column A.
 
Upvote 0
I am just looking for help constructing a formula or advice on an alternative method that will do what I need which I can reuse for other results.
 
Upvote 0
Following the structure you had in your original formula, I think this will work.

"=SUMPRODUCT(--(LEN(Sheet1!A:A)-LEN(SUBSTITUTE(Sheet1!A:A,A3,""))>0),Sheet1!B:B)"

But, I wouldn't use it. Doing a sumproduct on the whole column is a lot of processing. I think this would be a better option.

"=SUMPRODUCT(--(LEN(Sheet1!$A$13:$A$28)-LEN(SUBSTITUTE(Sheet1!$A$13:$A$28,A4,""))>0),Sheet1!$B$13:$B$28)"

But you will need to adjust the start and end rows in the references to match your total data set. I only had the rows you posted to go by.

That being said, I think this could be done better by restructuring the data in the pivot table. But that's a longer conversation....
 
Upvote 0
Oh, just realized your pivot table data was on Sheet4. Changed formulas to match your naming convention.

"=SUMPRODUCT(--(LEN(Sheet4!A:A)-LEN(SUBSTITUTE(Sheet4!A:A,A3,""))>0),Sheet4!B:B)"

"=SUMPRODUCT(--(LEN(Sheet4!$A$13:$A$28)-LEN(SUBSTITUTE(Sheet4!$A$13:$A$28,A4,""))>0),Sheet4!$B$13:$B$28)"
 
Upvote 0
One more thing. Do not make this an array formula like your original formula was. An array formula is not required when using Sumproduct (in this case).
 
Upvote 0
I appreciate the help! I will give this a go when I'm back in the office next Wednesday and be sure to let you know the results. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top