Count the number of instances of specific text and summarise

[penstar1000]

Hi all

I need help with reproducing what would be a Countif statement with a wildcard in excel

I have a data column with comma delimited values like this:

Region

Northern Adelaide
Northern Adelaide, Western Adelaide, Eyre and Western
Adelaide Hills, Eyre and Western, Northern Adelaide

I want to count the number of times each region appears and create a summary table to look something like this:

Region                                 Number of applications                $ sum
Northern Adelaide       3                                                                  $xx

Western Adelaide         1                                                                  $xx
 Eyre and Western         2                                                                 $xx             

etc etc

I've tried various combinations of search and filter but I'm currently getting the wrong result when I check using the Countif function in excel

Thanks for any help!                                  

[Zubair_Muhammad · Accepted solution]

@penstar1000 

Try this calculated column

Please see attached file as well

Number of Applications =
VAR myregion = [Region]
RETURN
    COUNTROWS ( FILTER ( Data, CONTAINSSTRING ( Data[Region], myregion ) ) )