Help finding first letter of data

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home (updated). I have a workbook containing 3 worksheets. One worksheet is titled "50-69" and contains 3000 rows with data of songs. Another worksheet titled "Counts" contains formulas indicating totals based on various items of data from "50-69". Column A of "50-69" contains the title of each song. I have tried several different functions trying to identify how many songs start with each letter of the alphabet and a few special characters such as Parenthesis and Double-Quote. All I need is one formula to identify the count of songs starting with character "A" and I can then extrapolate the formula to find the rest. I went through the Help feature but found no answer.
Thank you for any help.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Dan Wilson]

just another option to try

Countifs.xlsm
	A	B	C	D	E
1	(				1
2	1				0
3	a				2
4	h				1
5	y				3
1d

Cell Formulas
Range	Formula
E1:E5	E1	=SUMPRODUCT(--(LEFT('50-69'!$A$2:$A$4000)=A1))

Good day Dave Patton and thank you for responding. It appears there are several ways to solve my question. I will experiment with your suggestion and hopefully learn something new.

 

[Peter_SSs]

I narrowed the search to each character of the alphabet, a parenthesis and numbers 1-9.

In that case, if you want, you can get all the counts with a single formula in a single cell, rather than copying down. The formula in B1 below automatically 'spills' all the other results down the column.

Dan Wilson.xlsm
	A
1	Song
2	A Hard Day's Night
3	(I Can’t Get No) Satisfaction
4	You Belong To Me
5	10 Commandments
6	Hey Jude
7	1000 Nights
8	Another Brick in the Wall
9	Yesterday
10	Yeah!
11
50-69

Dan Wilson.xlsm
	A	B
1	A	2
2	B	0
3	C	0
4	D	0
5	E	0
6	F	0
7	G	0
8	H	1
9	I	0
10	J	0
11	K	0
12	L	0
13	M	0
14	N	0
15	O	0
16	P	0
17	Q	0
18	R	0
19	S	0
20	T	0
21	U	0
22	V	0
23	W	0
24	X	0
25	Y	3
26	Z	0
27	1	2
28	2	0
29	3	0
30	4	0
31	5	0
32	6	0
33	7	0
34	8	0
35	9	0
36
Counts

Cell Formulas
Range	Formula
B1:B35	B1	=COUNTIF('50-69'!A2:A5000,A1:A35&"*")
Dynamic array formulas.