Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Tim Tim is offline
external usenet poster
 
Posts: 408
Default count specific text that occurs in a range of cells

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




  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default count specific text that occurs in a range of cells

Hi Tim,


=Countif(A1:A10,"TEXT")

HTH
Cheers
Carim

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells


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


Hi Tim,

Try...

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

Ken Johnson

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells


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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

=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






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

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




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default count specific text that occurs in a range of cells

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))))

--
Regards

Roger Govier


"Ken Johnson" wrote in message
oups.com...

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



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells


Roger Govier wrote:
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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

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...

--
Festina Lente


"Ken Johnson" wrote:


Roger Govier wrote:
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


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells


PapaDos wrote:
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...

--
Festina Lente



Hi Festina,

Nice repair job!

Thanks for that

Ken Johnson



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

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

Thanks,
Luc.
--
Festina Lente


"Ken Johnson" wrote:


PapaDos wrote:
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...

--
Festina Lente



Hi Festina,

Nice repair job!

Thanks for that

Ken Johnson


  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells

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

Thanks,
Luc.
--
Festina Lente


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

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

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


"Ken Johnson" wrote:

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

Thanks,
Luc.
--
Festina Lente


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


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default count specific text that occurs in a range of cells

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


"PapaDos" wrote:

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


"Ken Johnson" wrote:

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

Thanks,
Luc.
--
Festina Lente


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


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default count specific text that occurs in a range of cells

Hi Luc

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


Perhaps
Make haste, slowly.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
Hi Ken,

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





  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default count specific text that occurs in a range of cells


PapaDos wrote:
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


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

Ken

  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 89
Default count specific text that occurs in a range of cells

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






"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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to count cells containing #'s in a specific range woogiebooboo Excel Worksheet Functions 1 April 27th 06 08:23 PM
count by specific text color in range of cell Tii99 Excel Discussion (Misc queries) 2 April 4th 06 09:58 AM
UDF is updateing cells on another sheet with count from current sheet. John Excel Discussion (Misc queries) 3 March 20th 06 04:58 PM
How to I copy text from a range of cells to another single cell? WRT Excel Discussion (Misc queries) 2 December 18th 05 07:17 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"