| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cell entries
Dear experts,
I have one column, C. In this column is a VLOOKUP to another range of cells in another workbook. The Lookup returns whatever comment has been typed into a particular cell on the other sheet. I want to count the number of times comments have been entered and display it underneath the column C. When I use COUNTA, it returns the total number of cells because the vlookup returns the number zero if there is no comment so this gets counted as an entry. The comments I want to count are entries like Fail, resit. Not everyone gets a comment. How can I be more specific about what I want COUNTA to count i.e. don't count zeros. Thanks Martina PS I could count the entries from the original sheet but I just wanted to know if it was possible to count entries in columns which have vlookups creating the data. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting cell entries
Try this:
=COUNTIF(C1:C100,"*") This will count *only* TEXT entries. -- Biff Microsoft Excel MVP "jc132568" wrote in message ... Dear experts, I have one column, C. In this column is a VLOOKUP to another range of cells in another workbook. The Lookup returns whatever comment has been typed into a particular cell on the other sheet. I want to count the number of times comments have been entered and display it underneath the column C. When I use COUNTA, it returns the total number of cells because the vlookup returns the number zero if there is no comment so this gets counted as an entry. The comments I want to count are entries like Fail, resit. Not everyone gets a comment. How can I be more specific about what I want COUNTA to count i.e. don't count zeros. Thanks Martina PS I could count the entries from the original sheet but I just wanted to know if it was possible to count entries in columns which have vlookups creating the data. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting cell entries
However, will also count nulls ( "" ), although technically, those zero
length strings *are* text. To count only text, and *not* count nulls: =Countif(C1:C100,"*?") -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... Try this: =COUNTIF(C1:C100,"*") This will count *only* TEXT entries. -- Biff Microsoft Excel MVP "jc132568" wrote in message ... Dear experts, I have one column, C. In this column is a VLOOKUP to another range of cells in another workbook. The Lookup returns whatever comment has been typed into a particular cell on the other sheet. I want to count the number of times comments have been entered and display it underneath the column C. When I use COUNTA, it returns the total number of cells because the vlookup returns the number zero if there is no comment so this gets counted as an entry. The comments I want to count are entries like Fail, resit. Not everyone gets a comment. How can I be more specific about what I want COUNTA to count i.e. don't count zeros. Thanks Martina PS I could count the entries from the original sheet but I just wanted to know if it was possible to count entries in columns which have vlookups creating the data. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting cell entries
Thank you both, fixed my problem.
Many thanks Martina "RagDyeR" wrote: However, will also count nulls ( "" ), although technically, those zero length strings *are* text. To count only text, and *not* count nulls: =Countif(C1:C100,"*?") -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... Try this: =COUNTIF(C1:C100,"*") This will count *only* TEXT entries. -- Biff Microsoft Excel MVP "jc132568" wrote in message ... Dear experts, I have one column, C. In this column is a VLOOKUP to another range of cells in another workbook. The Lookup returns whatever comment has been typed into a particular cell on the other sheet. I want to count the number of times comments have been entered and display it underneath the column C. When I use COUNTA, it returns the total number of cells because the vlookup returns the number zero if there is no comment so this gets counted as an entry. The comments I want to count are entries like Fail, resit. Not everyone gets a comment. How can I be more specific about what I want COUNTA to count i.e. don't count zeros. Thanks Martina PS I could count the entries from the original sheet but I just wanted to know if it was possible to count entries in columns which have vlookups creating the data. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting cell entries
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jc132568" wrote in message ... Thank you both, fixed my problem. Many thanks Martina "RagDyeR" wrote: However, will also count nulls ( "" ), although technically, those zero length strings *are* text. To count only text, and *not* count nulls: =Countif(C1:C100,"*?") -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... Try this: =COUNTIF(C1:C100,"*") This will count *only* TEXT entries. -- Biff Microsoft Excel MVP "jc132568" wrote in message ... Dear experts, I have one column, C. In this column is a VLOOKUP to another range of cells in another workbook. The Lookup returns whatever comment has been typed into a particular cell on the other sheet. I want to count the number of times comments have been entered and display it underneath the column C. When I use COUNTA, it returns the total number of cells because the vlookup returns the number zero if there is no comment so this gets counted as an entry. The comments I want to count are entries like Fail, resit. Not everyone gets a comment. How can I be more specific about what I want COUNTA to count i.e. don't count zeros. Thanks Martina PS I could count the entries from the original sheet but I just wanted to know if it was possible to count entries in columns which have vlookups creating the data. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting entries and copying | New Users to Excel | |||
| Counting entries | Excel Worksheet Functions | |||
| Counting unique entries | Excel Discussion (Misc queries) | |||
| Counting Entries | Excel Worksheet Functions | |||
| Counting Entries in a Cell | Excel Worksheet Functions | |||
Hybrid Mode
