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?

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

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" wrote in message
...
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?