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.

 

[tdcockers]

This will only work because you have Office365, but I would use something like this:

=SUM(COUNTIF('50-69'!A:A,{"=A*","=B*"}))

Note the curly brackets around the search criteria in the COUNTIF that create an array of multiple searches. The SUM function adds the totals together, in this case everything in Column A starting with A or B. You might need to use FIND rather than just a simple "=B" for the special characters depending what characters they are.

 

[AhoyNC]

Another option.

Book1
	A	B	C	D	E
1	Song		A	H	Y
2	A Hard Day's Night		2	1	1
3	Hey Jude
4	Another Brick in the Wall
5	Yesterday
Sheet4

Cell Formulas
Range	Formula
C2:E2	C2	=COUNTA(FILTER($A$2:$A$5,LEFT($A$2:$A$5)=C$1,0))

 

[Peter_SSs]

.. another possibility

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	(	1
2	1	2
3	A	2
4	H	1
5	Y	3
6
Counts

Cell Formulas
Range	Formula
A1:B5	A1	=LET(r,'50-69'!A2:A4000,u,UNIQUE(SORT(LEFT(r,1)))&"",FL,FILTER(u,u<>""),HSTACK(FL,COUNTIF(r,FL&"*")))
Dynamic array formulas.

 

[alansidman]

Using Peter's example, here is an alternative means with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Song ", Splitter.SplitTextByPositions({0, 1}, false), {"Song .1", "Song .2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {"Song .1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book6
	A	B	C	D
1	Song		Song .1	Count
2	A Hard Day's Night		A	2
3	(I Can’t Get No) Satisfaction		(	1
4	You Belong To Me		Y	3
5	10 Commandments		1	2
6	Hey Jude		H	1
7	1000 Nights
8	Another Brick in the Wall
9	Yesterday
10	Yeah!
Sheet1

 

[Dan Wilson]

This will only work because you have Office365, but I would use something like this:

=SUM(COUNTIF('50-69'!A:A,{"=A*","=B*"}))

Note the curly brackets around the search criteria in the COUNTIF that create an array of multiple searches. The SUM function adds the totals together, in this case everything in Column A starting with A or B. You might need to use FIND rather than just a simple "=B" for the special characters depending what characters they are.

Good Day tdcockers and Thank You for responding. Your formula worked very well. I never would have thought of including COUNTIF within a SUM function. I narrowed the search to each character of the alphabet, a parenthesis and numbers 1-9. I also appreciate knowing that I can select more than one character to search for. Well done. I can always count on learning something new from this forum.

 

[Dan Wilson]

Another option.

Book1
	A	B	C	D	E
1	Song		A	H	Y
2	A Hard Day's Night		2	1	1
3	Hey Jude
4	Another Brick in the Wall
5	Yesterday
Sheet4

Cell Formulas
Range	Formula
C2:E2	C2	=COUNTA(FILTER($A$2:$A$5,LEFT($A$2:$A$5)=C$1,0))

Good day AhoyNC and thank you for responding. For now, I will use the formula submitted by tdcockers, but I will experiment with your suggestion. It never hurts to learn something new.

 

[Dan Wilson]

.. another possibility

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	(	1
2	1	2
3	A	2
4	H	1
5	Y	3
6
Counts

Cell Formulas
Range	Formula
A1:B5	A1	=LET(r,'50-69'!A2:A4000,u,UNIQUE(SORT(LEFT(r,1)))&"",FL,FILTER(u,u<>""),HSTACK(FL,COUNTIF(r,FL&"*")))
Dynamic array formulas.

Good day Peter_SSs and thank you for responding. For now I will go with the formula submitted by tdcockers. I will experiment with your suggestion. It looks very interesting as well as complicated. I do fairly well with Macros, but I have a lot to learn about formulas.

 

[Dan Wilson]

Using Peter's example, here is an alternative means with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Song ", Splitter.SplitTextByPositions({0, 1}, false), {"Song .1", "Song .2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {"Song .1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book6
	A	B	C	D
1	Song		Song .1	Count
2	A Hard Day's Night		A	2
3	(I Can’t Get No) Satisfaction		(	1
4	You Belong To Me		Y	3
5	10 Commandments		1	2
6	Hey Jude		H	1
7	1000 Nights
8	Another Brick in the Wall
9	Yesterday
10	Yeah!
Sheet1

Good day alansidman thank you for responding. Your suggestion goes way beyond my expertise with Excel, but I will play with it and try to figure it out. I have never even heard of Power Query. It will be a good exercise.

 

[Dave Patton]

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))