Formula to count number of cells with specific text and more than 1 condition.

Hi All,

I request your kind expertise to find me a solution for a peculiar problem that exists. Basically, I am trying to count number of cells using a formula which contains specific text. Refer attached excel sheet for example. The table contains department, name of employee, the fruits he likes and whether he is active or retired in the organisation. I need to calculate number of employees who are active or retired, within a specific department who likes a particular fruit. The above example is used only for our exercise, whereas the real data has other fields where I need to apply the formula. The real problem is , the data (in this case the fruits) can be more than one but it exists inside one cell which cannot be copied/pasted in different cells as the amount of data is quite large.

Can anyone please help me with some basic excel function which will return me number of employees in a particular department who like a particular fruit (irrespective of the fact that he may like other fruits as well).

Thanks for suggesting but it does not work