How do I count cells beginning with specific letter?

For example: I have a number of cells that contains names, and I have to 
count the name that begins with letter "A", what do I do?

many thanks
0
Jay1 (212)
8/26/2005 5:38:11 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
531 Views

Similar Articles

[PageSpeed] 31

one way:

    =COUNTIF(A1:J100,"A*")

In article <B61CEE68-3044-4A71-9DA3-8F2B8AD51408@microsoft.com>,
 jay <jay@discussions.microsoft.com> wrote:

> For example: I have a number of cells that contains names, and I have to 
> count the name that begins with letter "A", what do I do?
> 
> many thanks
0
jemcgimpsey (6723)
8/26/2005 5:54:03 PM
try
=SUMPRODUCT((LEFT(J2:J5)="a")*1)

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"jay" <jay@discussions.microsoft.com> wrote in message
news:B61CEE68-3044-4A71-9DA3-8F2B8AD51408@microsoft.com...
> For example: I have a number of cells that contains names, and I have to
> count the name that begins with letter "A", what do I do?
>
> many thanks


0
Don
8/26/2005 5:54:49 PM
I have a nuber of cells that contain names, I have to count the names that 
begin with with letter, a,through z, How do I do that?


"jay" wrote:

> For example: I have a number of cells that contains names, and I have to 
> count the name that begins with letter "A", what do I do?
> 
> many thanks
0
jonathan1 (95)
10/15/2008 2:26:00 AM
=countif(a1:b99,"a*")



jonathan wrote:
> 
> I have a nuber of cells that contain names, I have to count the names that
> begin with with letter, a,through z, How do I do that?
> 
> "jay" wrote:
> 
> > For example: I have a number of cells that contains names, and I have to
> > count the name that begins with letter "A", what do I do?
> >
> > many thanks

-- 

Dave Peterson
0
petersod (12005)
10/15/2008 3:04:55 AM
i think you mis understood, i need to know the number of first names in my 
data, that begin with each particuliar letter of the alphabet, "A" through "z"

the number of occurances of each letter in the alphabet? A-Z

ex. 

a 10
b 9
c 3
d 29


"Dave Peterson" wrote:

> =countif(a1:b99,"a*")
> 
> 
> 
> jonathan wrote:
> > 
> > I have a nuber of cells that contain names, I have to count the names that
> > begin with with letter, a,through z, How do I do that?
> > 
> > "jay" wrote:
> > 
> > > For example: I have a number of cells that contains names, and I have to
> > > count the name that begins with letter "A", what do I do?
> > >
> > > many thanks
> 
> -- 
> 
> Dave Peterson
> 
0
jonathan1 (95)
10/15/2008 3:26:00 AM
I think this is a John Walkenbach construct but not positive.

I have altered it return just the first letter of a string.

Sub Count_First_Letters()
Dim letCount(1 To 26) As Long
Dim wkSht As Worksheet
Dim ii As Long
Dim Cell As Range
Dim WrkRng As Range
    For Each wkSht In Worksheets
        With wkSht
            If .Name = "ListLetters" Then
                Application.DisplayAlerts = False
                Sheets("ListLetters").Delete
            End If
        End With
    Next
    Application.DisplayAlerts = True
    Set WrkRng = ActiveSheet.UsedRange

    For Each Cell In WrkRng
        For ii = 1 To 1
            If Mid(UCase(Cell), ii, 1) Like "[A-Z]" Then
                letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) = _
                        letCount(Asc(Mid(UCase(Cell), ii, 1)) - 64) + 1
            End If
        Next ii
    Next Cell
    Set CopytoSheet = Worksheets.Add
    CopytoSheet.Name = "ListLetters"
    CopytoSheet.Activate
    Range("B1").Resize(26, 1).Value = Application.Transpose(letCount)
    With Range("A1").Resize(26, 1)
        .Formula = "=char(row()+64)"
        .Value = .Value
    End With
End Sub


Gord Dibben  MS Excel MVP

On Tue, 14 Oct 2008 20:26:00 -0700, jonathan
<jonathan@discussions.microsoft.com> wrote:

>i think you mis understood, i need to know the number of first names in my 
>data, that begin with each particuliar letter of the alphabet, "A" through "z"
>
>the number of occurances of each letter in the alphabet? A-Z
>
>ex. 
>
>a 10
>b 9
>c 3
>d 29
>
>
>"Dave Peterson" wrote:
>
>> =countif(a1:b99,"a*")
>> 
>> 
>> 
>> jonathan wrote:
>> > 
>> > I have a nuber of cells that contain names, I have to count the names that
>> > begin with with letter, a,through z, How do I do that?
>> > 
>> > "jay" wrote:
>> > 
>> > > For example: I have a number of cells that contains names, and I have to
>> > > count the name that begins with letter "A", what do I do?
>> > >
>> > > many thanks
>> 
>> -- 
>> 
>> Dave Peterson
>> 

0
Gord
10/15/2008 2:22:45 PM
Hi Jonathan

Just modify Dave's formula.
Type a, b , c etc down column C, then use

=COUNTIF(A:A,C1&"*")
Copy down through cells C2:C26
-- 
Regards
Roger Govier

"jonathan" <jonathan@discussions.microsoft.com> wrote in message 
news:EEAB652A-B232-4225-8E8A-3A0572ABDD0E@microsoft.com...
> i think you mis understood, i need to know the number of first names in my
> data, that begin with each particuliar letter of the alphabet, "A" through 
> "z"
>
> the number of occurances of each letter in the alphabet? A-Z
>
> ex.
>
> a 10
> b 9
> c 3
> d 29
>
>
> "Dave Peterson" wrote:
>
>> =countif(a1:b99,"a*")
>>
>>
>>
>> jonathan wrote:
>> >
>> > I have a nuber of cells that contain names, I have to count the names 
>> > that
>> > begin with with letter, a,through z, How do I do that?
>> >
>> > "jay" wrote:
>> >
>> > > For example: I have a number of cells that contains names, and I have 
>> > > to
>> > > count the name that begins with letter "A", what do I do?
>> > >
>> > > many thanks
>>
>> -- 
>>
>> Dave Peterson
>> 
0
Roger
10/15/2008 5:25:07 PM
Reply:

Similar Artilces:

Split cells #2
In Excel 2007, how do I split one cell into several rows? If you mean when you type, then you can press alt + enter for a new line within the same cell. -- Regards, Peo Sjoblom "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:4253279B-7B77-4A34-B085-46A71F90BD7C@microsoft.com... > In Excel 2007, how do I split one cell into several rows? "Peo Sjoblom" wrote: > If you mean when you type, then you can press alt + enter for a new line > within the same cell. > > > I typed in the cell and then selected ALT+Enter an...

How to find highest, lowest and last cell in row?
Hi I'm having trouble trying to find an excel function that will let me return the highest, lowest and last cell in a row of cells spanning M10..CF10 (60 cells). All the consecutive cells will have a value, but the row will only be partially full with some number of still empty cells at the end. Is there someone who knows how I can solve this problem. Thanks for your kind help. Please reply to the newsgroup as my email address is false to avoid spam. Thanks again Sam Hi 1. Min value: MIN(M10:CF10) 2. Max value: MAX(M10:CF10) 3. Last value (if you only have numeric values): =I...

Creating a drop down like menu for row of cells?
I have a bit of a problem with an excel worksheet that I have going, th whole sheet is about 450 rows with various bits of input data, an sub-totals for various sections calculated throughout it. In order to reduce the size and make it a little bit easier to navigat through, I am wondering if there is anyway to make rows drop down, lik say with an arrow to the side that a user can click on to have a grou of cells drop down, that way I can only have sub totals and su headings showing. Is this possible to do and how? I've tired searching through help but can't find anything, and don&#...

Get Range object from Cell Row and Column No.
Hi, I'm using Excel 2003, I have a function which I want it to return a range object which points to a cell, the cell is identified by the code which determins which column number and row number make up the Cells RowIndex and ColumnIndex attributes. For some reason I keep receiving an application-defined or object-defined error, my code is below, any advice would be appreciated: Function GetJournalInsertRange(ColumnHeader As String) As Range Dim ColumnNumber As Integer Dim RowNumber As Integer ColumnNumber = 0 For Each header In Range("JournalHdr&q...

numbers represented by letters.
hello folks :) i have a shift pattern. a shift =8 hrs. b shift=11 hrs. c shift=9 hrs. c/d shift=9hrs. d shift=9 hrs. o/d, r/d and a/l= 0 as these values represent time off work. i need to make a table. column 1 is employee names. columns 2-8 are days of the week. column 9 is total hours. i have made the table and got the total column doing its thing but how do i get it so i can input a letter such as C (which is the 9 hr shift) and have the total calculated based on the number that the letter C represents? thanks for any help you guys can give me and happy new year :) --- Message posted...

split text in cell into 2 #2
I am needing help with formula. I have a cell that takes values from several other cells to create one long line text (for a command used in other app). For ex: a1=jack b1=ran c1="There was a person named"&a1&"who was hungry and"&b1&" to Mcdonalds." This process works great except when the line is greater than 72. I need an if statment that says if c1 length is greater than 72 then to split / replace line AFTER name(a1). Where the split occurs a +, and carriage return needs to be added. So in the example above (pretend length will be over 72,...

select specific cells and consolidate same over many worksheets
Given that my excel skills are limited at present, I have a task for work that I hope you can help with. A series of saved excel files that are based on weeks - Monday to Sunday. The sheets are arranged with 1st column being a store with various stores runing down in rows(not each week shows the same sytores however there are many times where the same store is shown in seperate sorkbooks). The days of the week are also aranged in columns- Mon, Tue Wed, Thur, etc. The cells below each day for the store shows total sales . So the workbook for the week will show the total sales per store p...

Creating Letter Activities using workflow
Hello, I have been trying to create a process using workflow rules where certain events will trigger a rule to create a letter activity. So far, I have had limited success. Some rules seem to work fine and some will be inconsistent in that they will either fire when they are supposed to or they won't. Most of the rules I have tried are based on a change of status to trigger them. For example, I have several rules that are supposed to create a letter activity when the opportunity's status is changed to "Won" When I look in Workflow Monitor, I can see the rules running...

Conditional formatting
Hi! I'd like to hide the contents of a cell if another cell on that row is empty. I thought I'd be able to use conditional formatting, but I don't see how to reference another cell. I set the page background to be white, the text to be black and the was going to use conditional formating to say "if the cell in column three is blank, set the text color in this cell (column one) to white". How can I accomplish this? Thanks! Noozer Select your cell(s) to be white-fonted. Say A1:A10 Format>Conditional Format>Formula is: enter =C1="" Pick a white f...

white font in certain cells
Hello I have a problem with the fonts being white in various cells. I'm wondering if a prankster at work is messing with me. At first you think that the cell is just blank, but after I did a select all and all the cells were highlighted, I could see that the font is actually white in random cells. I use excel a whole lot and I don't see anything that would cause this. Using format cell, nothing appears to be different from the other cells that are okay. If I do a copy and paste from a good cell, the font remains white. I read up in help about a person could type in three ...

counting a range that has only general format
I'm attempting to count cells in a range that have a general format. Need help with countif. -- 1 putt Hi, AFAIK that can't be done with countif. How about a UDF. Alt +F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code beolw in call with =CountGeneral(A!:A10) Function CountGeneral(rng As Range) As Long For Each c In rng If c.NumberFormat = "General" Then CountGeneral = CountGeneral + 1 End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothes...

Drawing Revisions (aslo for Specifications)
Drawing Revisions are tracked first "in house" with a Letter. Then when issued to a client they are issued with a Number. For Example: blank line blank line etc. 2 Issued for Bids 1 Issued for Client Approval C Issued for Departmental Check B Issued for Review A Issued for Comment Please bear in mind the Revision list may have several blank lines first. (For future revisions). The latest issue is always at the top of the list. The Problem There are Revision cells on other Excel worksheets. What formula do I put in the other worksheet Revision cell to pick up the latest R...

Cell issues
I'm having an issue getting everything that I have typed to show up i the cell. I've checked everything I know to check but curious if ther is a limit to how much you can put in a cell? Or is there somethin else that I need to check -- mysticra ----------------------------------------------------------------------- mysticray's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=527 View this thread: http://www.excelforum.com/showthread.php?threadid=26727 Hi the max is 1,024 characters. you can extend this limit by manually inserting linebreaks with aLT+eNT...

Count function problem
I have a formula in a cell which reads: "=IF(COUNT(D13:G13)>=2,J13+MAX(H13,$B$43),0)" When I created this workbook template, the cells from D13 to G13 were blank until I filled them in, and this formula worked fine. However, I've made a change to the workbook. Now I've made D13 through G13 equal to the values in a range of cells from other sheets in the workbook (for e.g. D13 is "=Sheet1!D13". The above formula is now generating the number from B43 by default, even though the cells from D13 to G13 are blank. The "COUNT" help in Excel says: "...

Excel formatting of text-- one cell needed
I want to paste txt or doc text into one cell. Instead it populates the spreadsheet. A colleague who does not know English tried using CNTRL+X and was able to put the pasted text into one cell but could not duplicate it. Any ideas? Sample paste: -- I want to take this list -- Or group of statements -- Into one field in Excel But I get Cell A1 -- I want to take this list A2 -- Or group of statements A§ -- Into one field in Excel Thank you Double click in the cell to enter Edit Mode (or select the Formula box in the Formula bar) before pasting. In article <1D341060-EB4F-41...

Formula for changing a cell format #2
How do I change a cell format if certain text is displayed in a nother cell? e.g; cells A4 and B4 (will change to a black pattern background) if C4 displays "Test"? Hi, look at format, conditional formatting, and set up what ever you want. "Confused1" wrote: > How do I change a cell format if certain text is displayed in a nother cell? > > e.g; cells A4 and B4 (will change to a black pattern background) if C4 > displays "Test"? ...

Formula to update cell w/ActiveCell.Value?
Can I put a formula in a certain cell that will, on recalculation, update that cell with the value of the current ActiveCell? Ed Ed, Don't think so. But this sub will do it. Paste it into the sheet module. Change the location as necessary. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False ' prevent retriggering Range("A1") = ActiveCell.Text Application.EnableEvents = True End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Ed" <Ed_Millis@NOSPAM.Ho...

Compare a range of cells
How can i compare a range of cells an have them "reorganized" so that the one that correspond is listed on the same Row? AAA-AAA BBB-CCC CCC-DDD EEE-EEE become: AAA-AAA BBB- CCC-CCC -DDD EEE-EEE Two columns? Add a header to row 1 and try this macro that I've saved this from a few previous posts: Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim ColA As Range Dim ColB As Range Dim iRow As Long Dim myCols As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False Wit...

One Paste, Many Cells
Hello, When I write the following string in notepad: A <tab> B <newline> C <tab> D and copy/paste it to Excel, it fills 4 cells. I tried to set the value of a range in a VBA macro to a string containing an equivalent content ("a" & vbTab & "b" & vbNewLine & "c" & vbTab & "d") But this does not work. Could someone give me the magic lines to do that in programmatically ? Thank you in advance Sub VivienRequest() Dim vOne As Variant Dim vTwo As Variant Dim rng As Range vOne = Array("a", &qu...

Typing into excel, 1 cell per character
Does anyone know if there is a way to type in excel, so that the characters are one to a cell. If I typed hello starting at cell A1, each character would be in cells A1 to A5. Without the need to press the right arrow between characters. Is this even possible? :rolleyes: Thanks Nitesh :) -- Snook ------------------------------------------------------------------------ Snook's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26306 View this thread: http://www.excelforum.com/showthread.php?threadid=395800 Snook A macro could help, but you'd have to p...

Count first instance of entry only.
List of Names in Column A Dates in Column B. Times (hours only) in column C. How do I count the first instance for each name for the hour only? (Some names are duplicated and I want a name count in my pivot table) Assuming real times in col C In D2, copied down: =IF(COUNTA(A2,C2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*(HOUR(C$2:C2)=HOUR(C2)))<2,1,"")) Just sum col D for the result -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message news:b5f0dfe6-a3de-4d2e-9777-b473a5ede79f@s...

Cell Ref
Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws....

If cell D2 begins with 00 then E2 =
I am trying to automate a process on a spreadsheet. Where Cell D2 begins with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process then I want it to label everything else on column E "INTL". Any help is greatly appreciated. thanks. The code would look like the following: Sub AAA() Select Case UCase(Left(Range("D2").Text, 2)) Case "00", "11", "CA" Application.Intersect(ActiveSheet.UsedRange, _ Range("E2").EntireColumn).Val...

copy value from the cell above to the cell below in a column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below, all the way down the column at once. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Ctrl-D or edit-fill-fill down, works one at a time. But there is a way of copying each employee's name to the blank cell below all at once, without doing it individually. can anyon help? thanks See answers to yo...

Cell reference
For example, I know I can refer to another worksheet in a cell formula as such: =Data!C6 However, Is there a way to refer to the name of the sheet based on the name of the sheet being a variable (ie. the contents of another cell?) THanks Matt Lawson Hi =INDIRECT("'" & A1 & "'!C6) where A1 stores your sheet name "Matt Lawson" wrote: > For example, I know I can refer to another worksheet in a cell formula as such: > =Data!C6 > > However, Is there a way to refer to the name of the sheet based on the name > of the sheet being a var...