Sorry, I used "text" and "TEXT" by inadvertance, but the result is the same...

--
Festina Lente


"PapaDos" wrote:

=SUMPRODUCT( ( $A$1:$A$9 = "TEXT" ) * ( $A$2:$A$10 < "text" ) ) + ( $A$10 =
"TEXT" )
--
Festina Lente


"Tim" wrote:

Hi,

I'm trying to determine the correct formula to count the number of times the
word "TEXT" appears in a column. The problem i'm having is being able to
count a continuous range of cells that contains the word "TEXT", and return a
value of "1" in another worksheet. For example, in the table below cell # A2
contains "TEXT", which equates to 1 occurrence. I need a formula that will
count cells A 4, 5 & 6 and return a single value of 1, as opposed to 3. The
same would apply to cells A 9 & 10. Using the below example, the formula
will need to return a total value of 3 and not 6.

I would greatly appreciate any help.

Thanks

Tim

A

1 NUMBER
2 TEXT
3 NUMBER
4 TEXT
5 TEXT
6 TEXT
7 NUMBER
8 NUMBER
9 TEXT
10 TEXT