Count Records

[amerifax]

I have a very large file . I am looking for a way to page through my records by the first letter of the last name which is Column A. Is there a Hotkey or macro I can use to do this? Or is there a general way to do this? In other words I need to end up with a count of all names that start with the letter A, B, C as A = 10,050, B = 1079, C = 9473 etc...

This isn't an everyday occurrence that we need to look up this kind of information so we are just looking for a quick easy fix, something simple.

Thanks in advance for any help.

Bob

 

[Akashwani]

Hi Bob,

Does this work?...

=SUMPRODUCT(--(LEFT($A$2:$A$100,1)=B2))

B2 houses A

Change your range to suit.

I hope that helps.

Ak

 

[Haseeb Avarakkan]

Hello, try

B2, enter

=CHAR(64+ROWS(B$2:B2))

Copy down until get Z

C2, copy down

=COUNTIF(A:A,B2&"*")

 

[Marcelo Branco]

Or

=COUNTIF(A:A,CHAR(ROWS($1:65))&"*")
copy down

M.

 

[amerifax]

Thank all for help. CountIF worked great.

Bob

 

[amerifax]

One minute I got it next mintue...

I did have it working and it was great.

What is it the code is doing?

A:A is looking at the Letter to count>

=COUNTIF(A:A,CHAR(ROWS($1:65))&"*")

My thoughts are the range as in A1:A137729

My next concern is the $1:65
I was thinking it might be characters 1 to 65.

Any way it was working until I moved it.

Bob

 

[Aladin Akyurek]

...

E2: A
E3: B
E4: C

and so on.

F2, copy down:

=COUNTIF($A$2:$A$1000,E2&"*")

 

[amerifax]

I seem to have an update problem. That's why it was not working. I found that if i go to the command line and edit the formula the cell will update. Is there some place I can have the sheet update it's self automaticaly. Can I also do a manual update. Ctr Alt does nothing.

But if I change one charector it up dates.

=COUNTIF($A$1:$A$137730,I5&"*")

Just by backspacing on the 5, see code above, and retyping the 5 the cell will update.

Bob

 

[Aladin Akyurek]

I seem to have an update problem. That's why it was not working. I found that if i go to the command line and edit the formula the cell will update. Is there some place I can have the sheet update it's self automaticaly. Can I also do a manual update. Ctr Alt does nothing.

But if I change one charector it up dates.

=COUNTIF($A$1:$A$137730,I5&"*")

Just by backspacing on the 5, see code above, and retyping the 5 the cell will update.

Bob

You probably need to set Calculation on Automatic. Would you check that?

 

[amerifax]

I have. Thanks. I also saved the file to a new spreadsheet. I felt that more than likely there might have been some corruption involved.

But now for some reason I cannot get the sort icon to show, it's grayed out. I've been trying to sort the phone column, and just cannot get that sort icon to show.

Talk about feeling stupid. I've been working with computers since the beginning. But tonight, I feel like the guy that called tech support and got a problem fixed when the technician asked if the computer was plugged in.

Bob