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

[daniel_san]

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!

 

[oldbrewer]

my answers seem different to yours

[TABLE="width: 1617"]
<colgroup><col><col><col span="5"><col><col span="4"><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Not applicable - received as a gift[/TD]
[TD]Product features[/TD]
[TD]Choice of accessories[/TD]
[TD]Quality[/TD]
[TD]Ease of use[/TD]
[TD]Reliability[/TD]
[TD]Energy efficient and eco friendly features[/TD]
[TD]Russell Hobbs reputation[/TD]
[TD]Fast results/time saving[/TD]
[TD]Safe[/TD]
[TD]Length of guarantee[/TD]
[TD]Size of product/space saving[/TD]
[TD]Lightweight[/TD]
[TD]Stylish[/TD]
[TD]Powerful performance[/TD]
[TD]Value for money/special offer[/TD]
[TD]Previous experience of Russell Hobbs[/TD]
[TD]Well designed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Choice of accessories[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Choice of accessories;Easy to use[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Fast results/time saving;Length of guarantee;Lightweight[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]135[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]193[/TD]
[TD]0[/TD]
[TD]201[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]213[/TD]
[TD]135[/TD]
[TD]0[/TD]
[TD]156[/TD]
[TD]235[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]156[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]135[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]103[/TD]
[TD]135[/TD]
[TD]123[/TD]
[TD]164[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]172[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]144[/TD]
[TD]1[/TD]
[TD]161[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]169[/TD]
[TD]78[/TD]
[TD]194[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]199[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]221[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]144[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]161[/TD]
[TD]103[/TD]
[TD]166[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]209[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]140[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]136[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]153[/TD]
[TD]103[/TD]
[TD]182[/TD]
[TD]115[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]111[/TD]
[TD]35[/TD]
[TD]123[/TD]
[TD]78[/TD]
[TD]148[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]153[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Choice of accessories;Easy to use;Energy efficient and eco friendly features;Length of guarantee;Lightweight[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]98[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]156[/TD]
[TD]0[/TD]
[TD]164[/TD]
[TD]35[/TD]
[TD]176[/TD]
[TD]0[/TD]
[TD]201[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]206[/TD]
[TD]98[/TD]
[TD]0[/TD]
[TD]119[/TD]
[TD]228[/TD]
[/TR]
[TR]
[TD][/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][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]98[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]35[/TD]
[TD]106[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]78[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]131[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]547[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]711[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]476[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]730[/TD]
[TD="align: right"]780[/TD]
[TD="align: right"]844[/TD]
[TD="align: right"]1058[/TD]
[TD="align: right"]454[/TD]
[TD="align: right"]816[/TD]
[TD="align: right"]1248[/TD]
[TD="align: right"]864[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]1065[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[daniel_san]

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.

 

[oldbrewer]

I have counted "how many of each answer", what more than this do you want, please ?

 

[daniel_san]

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.

 

[quarencia]

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

 

[quarencia]

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

 

[quarencia]

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

 

[daniel_san]

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.

 

[daniel_san]

Wow! Thank you so much, really elegant and simple solution to my problem. I really appreciate your help!