Count cells containing specific text using Pivot without helper columns

[tiro]

Hi guys,
Long time lurker, first time poster here.

I'm working with results from a SharePoint survey that allows the respondent to check multiple boxes for a single question. In Excel, such answers are concatenated in a single cell with delimiters. For example, one answer could be
Apple;#Orange;#Banana
and another
Apple;#Banana

I need to count the number of times a certain word (eg Banana) occurs in a column with such concatenated values. It's very similar to this use case:

Pivot Tables - where Cell 'Contains' text...

Hello, can anyone please let me know if it is possible to create a pivot table but specifying a count where data within a column contains a specific string (similar to the option available when filtering)? Sample scenario - a question within a survey allows the user to select their top 3...

www.mrexcel.com

I would like to do this with a pivot table, but without using helper coloumns in the source data.

Is this in any way possible? I'm fine using VBA, and could also possibly get PowerPivot if needed - but would like to avoid it.

Thanks in advance!

 

[GraH]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Survey Results				Row Labels	Count of Survey Results			Fruit	Votes
2	Apple				Apple	2			Apple	6
3	Banana,#Apple,#Orange				Apple,#Orange	2			Orange	9
4	Apple,#Orange				Banana,#Apple,#Orange	2			Banana	6
5	Orange,#Banana				Orange,#Banana	3			Pineapple	1
6	Pineapple,#Banana,#Orange				Pineapple,#Banana,#Orange	1
7	Banana,#Apple,#Orange				Orange	1
8	Apple				Banana,#pinepple	1
9	Orange,#Banana				Grand Total	12
10	Apple,#Orange
11	Orange,#Banana
12	Orange
13	Banana,#pinepple
14
15
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=SUMPRODUCT((ISNUMBER(FIND(I2,$E$2:$E$7))*1),$F$2:$F$7)

 

[tiro]

Thanks, This works, but I need to count the cells using pivot, so that it's easier to filter the data before doing the count. Any way to count using pivot?

 

[tiro]

Thanks, This works, but I need to count the cells using pivot, so that it's easier to filter the data before doing the count. Any way to count using pivot?

Sorry, was on mobile, didn't see your whole screenshot at first. This might actually work, thanks! Unless there is some way to solve it completely within the pivot?

 

[GraH]

Then I would make life simple and split the text per delimiter in new rows via PowerQuery. Then simply group by per lemma.

let
    Source = Excel.CurrentWorkbook(){[Name="Survey"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Survey Results", Splitter.SplitTextByDelimiter(",#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Survey Results"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Survey Results"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

The green table would be the result.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Survey Results				Row Labels	Count of Survey Results			Fruit	Votes
2	Apple				Apple	2			Apple	6
3	Banana,#Apple,#Orange				Apple,#Orange	2			Orange	9
4	Apple,#Orange				Banana,#Apple,#Orange	2			Banana	6
5	Orange,#Banana				Orange,#Banana	3			Pineapple	1
6	Pineapple,#Banana,#Orange				Pineapple,#Banana,#Orange	1
7	Banana,#Apple,#Orange				Orange	1
8	Apple				Banana,#pinepple	1			Survey Results	Count
9	Orange,#Banana				Grand Total	12			Apple	6
10	Apple,#Orange								Banana	7
11	Orange,#Banana								Orange	9
12	Orange								Pineapple	1
13	Banana,#pinepple								pinepple	1
14
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=SUMPRODUCT((ISNUMBER(FIND(I2,$E$2:$E$7))*1),$F$2:$F$7)

 

[tiro]

Didn't have a chance to test this until today, but just wanted to let you know that your solution worked flawlessly. Thanks so much!

 

[GraH]

Hi @tiro, feedback and mark as solution are appreciated! Which one of the solutions did you finally go for?

 

[tiro]

Hi @tiro, feedback and mark as solution are appreciated! Which one of the solutions did you finally go for?

I used the Power Query solution for now, so I marked that as the solution. However, I think I will also use your first proposed solution at a later point, to allow for easy use of pivot slicers, etc!

 

[GraH]

Obviously, was fooled by my own post where I kept all solutions in one screenshot.
Slicers work on tables too. But I reckon you have more columns in the real situation. Remember you can feed the PQ to a pivot table too. So you sum the count in the end.