CODEWITHSUNDEEP
excelpivot-tabledax
Eden Maloney
Eden Maloney

Reputation: 13

In Excel is there a way to show PivotTable count of cells containing a text string?

I have data from a satisfaction survey. One of the questions allows respondents to check up to 5 boxes. The resulting data is a concatenation of the text of all the boxes that were checked, i.e., "box one;box three;box four" or "box two;box five", etc. Need a pivot table to show out of all responses, how many times box 1 was checked, etc. My data is in Table format and I check the box saying "Add this data to the Data Model" when generating the PivotTable. Then I tried adding a Measure to using DAX formula: 

[=COUNTAX(Survey,[What most influenced your service satisfaction?]="* box one *")]

to count how many times the text string "box one" appears in the column. I get an incorrect value = it is just returning the total number of rows in the table.

Upvotes: 1

Views: 808

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40234

The COUNTAX function counts every row that evaluates to anything non-blank. In your case, the expression evaluates to FALSE() for every row since the asterisks don't act as wild but as literal asterisks so every row gets counted.

How about counting the rows after filtering for ones that contain "box one"?

BoxOneCount =
COUNTROWS (
    FILTER ( Survey, CONTAINSSTRING ( Survey[What Influenced], "box one" ) )
)

Edit: The CONTAINSSTRING DAX function is a newer one that doesn't work in Excel. You should be able to use an alternative with FIND for Excel.

BoxOneCount =
COUNTROWS (
    FILTER (
        Survey,
        NOT ( ISERROR ( FIND ( "box one", Survey[What Influenced] ) ) )
    )
)

Upvotes: 1

Related Questions