count specific text that occurs in a range of cells

[Tim]

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

 

[Carim]

Hi Tim,


=Countif(A1:A10,"TEXT")

HTH
Cheers
Carim

 

[Ken Johnson]

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

Hi Tim,

Try...

=SUMPRODUCT(--(A1:A9="NUMBER"),--(A2:A10="TEXT"))

Ken Johnson

 

[Ken Johnson]

Hi Tim,

=SUMPRODUCT(--(A1:A10="NUMBER"),--(OFFSET(A1:A10,1,0)="TEXT"))

is probably a more logically consistent form of the same equation.

Ken Johnson

 

[PapaDos]

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

 

[PapaDos]

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

 

[Roger Govier]

Hi Ken

Nice solution.
If the OP was using the words number and text merely as representative
of cells containing either numeric or text entries, then your formula
could be generalised to

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(ISTEXT(OFFSET(A1:A10,1,0))))

 

[Ken Johnson]

Hi Ken

Nice solution.
If the OP was using the words number and text merely as representative
of cells containing either numeric or text entries, then your formula
could be generalised to

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(ISTEXT(OFFSET(A1:A10,1,0))))

Hi Roger,

Thanks for that.
Could be useful.

Ken Johnson

 

[PapaDos]

The solution is looking right with the example given but its logic is flawed.
It counts transitions from NUMBER to TEXT.

Try it with:

TEXT
TEXT
NUMBER
TEXT
TEXT
TEXT
TEXT
NUMBER
NUMBER
TEXT

Also, it is dependent on the cell following the table, wich could cause
problems.
And it works only for NUMBER and TEXT, if something else is in the table, it
fails...

 

[Ken Johnson]

The solution is looking right with the example given but its logic is flawed.
It counts transitions from NUMBER to TEXT.

Try it with:

TEXT
TEXT
NUMBER
TEXT
TEXT
TEXT
TEXT
NUMBER
NUMBER
TEXT

Also, it is dependent on the cell following the table, wich could cause
problems.
And it works only for NUMBER and TEXT, if something else is in the table, it
fails...

Hi Festina,

Nice repair job!

Thanks for that

Ken Johnson

 

[PapaDos]

LOL
Common mistake, but my name is not Festina...
;-]

Thanks,
Luc.

 

[Ken Johnson]

LOL
Common mistake, but my name is not Festina...
;-]

Thanks,
Luc.

Hi Luc,

What then is Festina Lente?

Is it some form of salutation?

BTW I was keen to retain the table address in the formula so I tried...

=SUMPRODUCT(--(OFFSET(A2:A11,-1,0)="TEXT"),--(OFFSET(A2:A11,1,0)="NUMBER"))

Because of the -1 row offset it can't be used when the table starts at
row 1, but for other tables it seems to work. The other problems you
pointed out, however, still stand.

Ken Johnson

 

[PapaDos]

Hi Ken,

"Festina Lente" is Latin and means something like "Hurry slowly".
It is difficult to translate precisely...

I understand your idea of referencing only the exact table range in the
solution.
We can make my solution a bit more complex, to achieve that.

Assuming the table is named "TABLE", that formula should do the job:
=SUMPRODUCT( ( OFFSET( TABLE, 0, 0, ROWS( TABLE ) - 1 ) = "TEXT" ) * (
OFFSET( TABLE, 1, 0, ROWS( TABLE ) - 1 ) <> "TEXT" ) ) + ( OFFSET( TABLE,
ROWS( TABLE ) - 1, 0, 1, 1 ) = "TEXT" )

--
Festina Lente

LOL
Common mistake, but my name is not Festina...
;-]

Thanks,
Luc.

Hi Luc,

What then is Festina Lente?

Is it some form of salutation?

BTW I was keen to retain the table address in the formula so I tried...

=SUMPRODUCT(--(OFFSET(A2:A11,-1,0)="TEXT"),--(OFFSET(A2:A11,1,0)="NUMBER"))

Because of the -1 row offset it can't be used when the table starts at
row 1, but for other tables it seems to work. The other problems you
pointed out, however, still stand.

Ken Johnson

 

[PapaDos]

The main problem with that kind of approach is that OFFSET() being volatile,
it can make things very slow...
--
Festina Lente

Hi Ken,

"Festina Lente" is Latin and means something like "Hurry slowly".
It is difficult to translate precisely...

I understand your idea of referencing only the exact table range in the
solution.
We can make my solution a bit more complex, to achieve that.

Assuming the table is named "TABLE", that formula should do the job:
=SUMPRODUCT( ( OFFSET( TABLE, 0, 0, ROWS( TABLE ) - 1 ) = "TEXT" ) * (
OFFSET( TABLE, 1, 0, ROWS( TABLE ) - 1 ) <> "TEXT" ) ) + ( OFFSET( TABLE,
ROWS( TABLE ) - 1, 0, 1, 1 ) = "TEXT" )

--
Festina Lente

LOL
Common mistake, but my name is not Festina...
;-]

Thanks,
Luc.

Hi Luc,

What then is Festina Lente?

Is it some form of salutation?

BTW I was keen to retain the table address in the formula so I tried...

=SUMPRODUCT(--(OFFSET(A2:A11,-1,0)="TEXT"),--(OFFSET(A2:A11,1,0)="NUMBER"))

Because of the -1 row offset it can't be used when the table starts at
row 1, but for other tables it seems to work. The other problems you
pointed out, however, still stand.

Ken Johnson

 

[Roger Govier]

Hi Luc

"Festina Lente" is Latin and means something like "Hurry slowly".
It is difficult to translate precisely...

Perhaps
Make haste, slowly.

 

[Ken Johnson]

Hi Ken,

"Festina Lente" is Latin and means something like "Hurry slowly".
It is difficult to translate precisely...

I understand your idea of referencing only the exact table range in the
solution.
We can make my solution a bit more complex, to achieve that.

Assuming the table is named "TABLE", that formula should do the job:
=SUMPRODUCT( ( OFFSET( TABLE, 0, 0, ROWS( TABLE ) - 1 ) = "TEXT" ) * (
OFFSET( TABLE, 1, 0, ROWS( TABLE ) - 1 ) <> "TEXT" ) ) + ( OFFSET( TABLE,
ROWS( TABLE ) - 1, 0, 1, 1 ) = "TEXT" )

That's excellent Luc, the table can even start on row 1.

Ken

 

[Kevryl]

Gidday Tim,

If you, like me get a bit bamboozled with the whole VB thing, and if you
have no macros that depend on the number of columns staying the same, here's
a different approach:

If column A contains the data you are testing, use column B (and hide the
column) to test it with the formula:
=if(A1="Text",1,0)
Replicate the formula down column B and sum it at the bottom with the formula
=Sum(B1:B100) (assuming you have 100 rows).

You can reference that total of course from anywhere in your worksheet,
making it easier perhaps by giving the range a name such as "TextTotal".
Then, wherever you want that number to appear you simply type in the formula
"=TextTotal".

Using this method you can of course test for any string you want to, simply
by substituting the required string for "Text" in the column B test formulae.

You could also extend the functionality, if you wanted by using columns C,
D, etc to test for other strings in column A, even summing the totals and
deucting from 100 to give the number of times that "none of the above"
appears in column A.

Cheers,
Keith