Formula to count number of occurrences of a word, with some additional conditions

Hi,

I have a range of cells in one sheet containing data as follows:

Please Login or Register  to view this content.

In another sheet, I need to write one formula to count the number of times ABC occurs, another for the number of times DEF_ABC occurs and yet another for the number of times ABC_XYZ occurs. Note that DEF_ABC and ABX_XYZ need to considered distinct from ABC (i.e. not included in the count for ABC). This is only a sample list and the actual number of such strings I need to search for is much more. Is it possible to write a formula that satisfies these conditions?

Many thanks in advance!

Srikanth

Formula wise COUNTIF should suffice, however, I would make the point that a Pivot Table would be just as easy (and more flexible).

My apologies. In my original post, I forgot to mention that each cell could potentially contain concatenations of these values as well. For example, one cell (say A1) might contain just ABC, while A2 contains ABC and DEF_ABC and so on. So the formula that counts the occurrences of ABC should result in the value 2 (and not 3), while the one that counts DEF_ABC should result in the value 1. I've tried to illustrate more clearly in the sample below. Please let me know if I need to make this clearer.

Please Login or Register  to view this content.

I believe COUNTIF is inadequate to handle this, I'm not so sure about Pivot Tables though. I think that, because of the (possible) concatenations, even Pivot Tables might not really help. Please correct me if I'm wrong.

Srikanth

If we assume you have in cell line breaks as delimiter and your strings are as you state in A1:A3 then with B1:D1 being ABC, DEF_ABC & ABC_XYZ you can generate the COUNT using:

Please Login or Register  to view this content.

which would return 2, 1 & 2 respectively (per your example)

Perfect! Works like a charm!

Thank you!!

Srikanth