How do I count wildcard text meeting certain criteria in EXCEL?

I can't figure out how to count occurrences of a wildcard matching certain
criteria. For example, let's say I want to count the number of cells
containing words ending in "ing" - even if those cells contain other words
besides the one ending in "ing". How do I do that? And then, out of that
count, how do I count only the number of those cells which also contain a
value of "X" in the same row one column to the right? Let me give a visual:

A B C
1 slow ending X
2 slow beginning X
3 fast ending

In this example, there are 3 cells in column A that contain a word that ends
in "ing", and out of those 3, 2 of them have an X in column B. What formula
would I use in this situation to account for the wildcard *ing, and to count
only those occurrences that also have an X in column B, to give the value of
2 in the cell containing this formula?

One way:

=SUMPRODUCT(ISNUMBER(SEARCH("ing",A1:A10))*(B1:B10="X"))

Adapt the ranges to suit, but it cannot be entire col refs (A:A, B:B).
Replace SEARCH with FIND if you need it to be case-sensitive.
(SEARCH is not case-sensitive)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cybermaksim" <[email protected]> wrote in message
news:[email protected]...
> I can't figure out how to count occurrences of a wildcard matching certain
> criteria. For example, let's say I want to count the number of cells
> containing words ending in "ing" - even if those cells contain other words
> besides the one ending in "ing". How do I do that? And then, out of that
> count, how do I count only the number of those cells which also contain a
> value of "X" in the same row one column to the right? Let me give a
visual:
>
> A B C
> 1 slow ending X
> 2 slow beginning X
> 3 fast ending
>
> In this example, there are 3 cells in column A that contain a word that
ends
> in "ing", and out of those 3, 2 of them have an X in column B. What
formula
> would I use in this situation to account for the wildcard *ing, and to
count
> only those occurrences that also have an X in column B, to give the value
of
> 2 in the cell containing this formula?