Count of All strings in a column broken by ;

Hi,

I need to do a total count of all Strings broken down by a ';' in a string, My data looks something like below

DATA:

EMP   DEP
100   A;B;C
200   A; C
300   C

OUTPUT:

DEP COUNT(EMP)
A      2
B      1
C      3

I have put the following formula in the cross tab and do count on EMP however I am not getting the result I am looking for I know if else doesn't work the way I want

@Formula
if DEP like '*A*' then 'A'
if DEP like '*B*' then 'B'
if DEP like '*B*' then 'C'

Thanks

I wouldn't use a cross-tab for this - I don't think it's going to work to count a record more than once. If you don't have a lot of possible values for this field, you would create a formula for each possible value that looks like this:

{@A for Count}
If InStr({DEP}, 'A') > 1 then {EMP}

Then in the report footer, you would list each possible department with a count of its "for Count" formula to get your totals.

-Dell