Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
in excel how do I count cells that begin with specific text. Ex: in a
column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
=SUMPRODUCT(--(LEFT(A1:A100)="app"))
"mmer at steelcase" wrote: in excel how do I count cells that begin with specific text. Ex: in a column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
=COUNTIF(A:A,"app*")
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sloth" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A100)="app")) "mmer at steelcase" wrote: in excel how do I count cells that begin with specific text. Ex: in a column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
this didn't work for me. do the 2 dashes in front of LEFT mean something
"Sloth" wrote: =SUMPRODUCT(--(LEFT(A1:A100)="app")) "mmer at steelcase" wrote: in excel how do I count cells that begin with specific text. Ex: in a column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
this works since I am looking for the beginning of the text. I thought I
would need to do something with LEFT function. this solution is simpler. thank you "Bob Phillips" wrote: =COUNTIF(A:A,"app*") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sloth" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A100)="app")) "mmer at steelcase" wrote: in excel how do I count cells that begin with specific text. Ex: in a column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
"mmer at steelcase" wrote in message ... this didn't work for me. do the 2 dashes in front of LEFT mean something See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
At first glance, I thought that would work. However, I noticed in your reply
that the ,3 was left out of the left function. If you don't specify the number of characters to return, left defaults to 1 (if I am not mistaken.) The -- coerces logical values (true or false) to numbers. As true evaluates to 1 (and false to 0) -true = -1 (and -false = -0, which of course is 0.) Applying the negative again changes the -1 to positive 1 and the -0 to 0. So, although I didn't test it, I would guess if you put ,3 after the 100 in the formula, it may return the correct result. BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0) fwiw. -- Kevin Vaughn "mmer at steelcase" wrote: this didn't work for me. do the 2 dashes in front of LEFT mean something "Sloth" wrote: =SUMPRODUCT(--(LEFT(A1:A100)="app")) "mmer at steelcase" wrote: in excel how do I count cells that begin with specific text. Ex: in a column with 100 entries, I want to count the number of cells that have the letters "app" from the word approved as the first three characters in the cell |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells that begin with specific text
Yeah, I should have gone back and rephrased the part where I was talking
about that. My bad. -- Kevin Vaughn "Bob Phillips" wrote: "Kevin Vaughn" wrote in message ... BTW, I just checked with false in a cell, D2, and -D2 returned 0 (not -0) fwiw. That is because 0 is neither negative nor positive, so you cannot have -0. Negative and positive is defined in relation to 0. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't see text in cells | Excel Discussion (Misc queries) | |||
Count by Colour (Text) with other criteria | Excel Discussion (Misc queries) | |||
count cells that contain text entries | Excel Worksheet Functions | |||
Can I count cells with specific format (e.g., yellow field?) | Excel Worksheet Functions | |||
How to count occcurence of specific text block in a column | Excel Discussion (Misc queries) |