software

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 quot;ingquot; - even if those cells contain other words
besides the one ending in quot;ingquot;. 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 quot;Xquot; 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 quot;ingquot;, 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(quot;ingquot;,A1:A10))*(B1:B10 =quot;Xquot;))

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
savefile.com/projects/236895
xdemechanik
---
quot;cybermaksimquot; gt; wrote in message
...
gt; I can't figure out how to count occurrences of a wildcard matching certain
gt; criteria. For example, let's say I want to count the number of cells
gt; containing words ending in quot;ingquot; - even if those cells contain other words
gt; besides the one ending in quot;ingquot;. How do I do that? And then, out of that
gt; count, how do I count only the number of those cells which also contain a
gt; value of quot;Xquot; in the same row one column to the right? Let me give a
visual:
gt;
gt; A B C
gt; 1 slow ending X
gt; 2 slow beginning X
gt; 3 fast ending
gt;
gt; In this example, there are 3 cells in column A that contain a word that
ends
gt; in quot;ingquot;, and out of those 3, 2 of them have an X in column B. What
formula
gt; would I use in this situation to account for the wildcard *ing, and to
count
gt; only those occurrences that also have an X in column B, to give the value
of
gt; 2 in the cell containing this formula?