|
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 |
count specific text that occurs in a range of cells
Hi Tim,
=Countif(A1:A10,"TEXT") HTH Cheers Carim |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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... |
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 |
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 |
| All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com