Help with COUNT functions

[Dan Wilson]

Good day. I am running Excel out of Office 365 (updated) and Windows 10 Home (updated). I have a workbook titled "50-69" that tracks music that is played in my weekly Podcast. The worksheet containing the data for each song is also titled "50-69". A second worksheet is Titled "Counts". The various Columns in the "50-69" worksheet contain elements of each song - Title, Artist, Year, Chart Rating. and others There is a Column to track if a song has been played in one of the Podcasts. The particular Columns of interest in the "50-59" worksheet are -

Column D is the year the song was recorded,
Column E is the Chart Rating of the song, If the song did not chart, the cell is blank.
Column H is the Show that played the song.

Currently there are 118 Podcasts. There are currently almost 3000 songs listed in the "50-69" worksheet.

In the "50-69" worksheet, if there is a number in Column H, the song has been played and the number identifies the Podcast. If the song has not been played, Column H is blank for that song. In the "Counts" worksheet, Column A is numbered 1 through 150 in Rows 2 through 151. Row A is for Column Headers. In Column B, the formula shown below is repeated with the reference being updated to search for the number in Column A.

In the "Counts" worksheet, I can identify the count of songs for each Chart Rating using the formula =COUNTIF('50-69'!E:E,A2). I am having trouble trying to identify a count of how many of each Chart Rating has not yet been played. I have experimented with all the various COUNT functions, but to no success. The problem may have something to do with the fact that I am dealing with a blank that may be interpreted as "0" in the formulas. Any help will be appreciated.
Thanks, Dan Wilson...

 

[Sufiyan97]

Can you post sample of your data including current result you're getting and expected results.

 

[Fluff]

Maybe

=COUNTIFS('50-69'!E:E,A2,'50-69'!H:H,"")

 

[awoohaw]

try this. I did it all in one worksheet, i hope you can figure it out:

Book1
	A	B	C	D	E	F	G	H
1	COUNTS				50-69
2	A				D	E		H
3	Chart Rating	Played	Not Played		Year Recorded	Chart Rating		Show Played
4	1	3	0		1973	1		Podcast 3
5	2	1	1		2018	6
6	3	3	0		1991	7		Podcast 2
7	4	0	1		2016	2		Podcast 5
8	5	2	1		1972	4
9	6	0	2		2003	2
10					1968	3		Podcast 2
11					1984	5		Podcast 4
12					2021	6
13					2006	3		Podcast 4
14					1968	5		Podcast 1
15					2008	5
16					1998	1		Podcast 2
17					1967	3		Podcast 2
18					1979	1		Podcast 5
Dan Wilson

Cell Formulas
Range	Formula
B4:B9	B4	=SUM((--($A4=$F$4:$F$18))*(--($H$4:$H$18<>"")))
C4:C9	C4	=SUM((--($A4=$F$4:$F$18))*(--($H$4:$H$18="")))

 

[Dan Wilson]

Can you post sample of your data including current result you're getting and expected results.

Good day Sufiyan97 and thank you for responding. Fluff came up with the answer I was looking for.
Dan Wilson...

 

[Dan Wilson]

Maybe

=COUNTIFS('50-69'!E:E,A2,'50-69'!H:H,"")

Good Day Fluff! Wow! Thank you for responding. Your solution is just what I was looking for. It works perfectly. I thought I had tried using the double quotes to substitute a blank, but I must have done something wrong. If I might, can I ask you to fix one more problem. If I use Row A to identfy the years from 1950 to 1969 using Columns D through W, is there a way to count how many songs there are that match each of the years for a given chart rating. For example, how many songs that charted number 1 in each year from 1950 through 1969 on Row 2? If you can show me how to do Row 2, I can then edit the formula as necessary for each of the 150 chart ratings. I realize I am asking quite a bit. I will not continue this any further.
Thank you,
Dan Wilson...

 

[Dan Wilson]

try this. I did it all in one worksheet, i hope you can figure it out:

Book1
	A	B	C	D	E	F	G	H
1	COUNTS				50-69
2	A				D	E		H
3	Chart Rating	Played	Not Played		Year Recorded	Chart Rating		Show Played
4	1	3	0		1973	1		Podcast 3
5	2	1	1		2018	6
6	3	3	0		1991	7		Podcast 2
7	4	0	1		2016	2		Podcast 5
8	5	2	1		1972	4
9	6	0	2		2003	2
10					1968	3		Podcast 2
11					1984	5		Podcast 4
12					2021	6
13					2006	3		Podcast 4
14					1968	5		Podcast 1
15					2008	5
16					1998	1		Podcast 2
17					1967	3		Podcast 2
18					1979	1		Podcast 5
Dan Wilson

Cell Formulas
Range	Formula
B4:B9	B4	=SUM((--($A4=$F$4:$F$18))*(--($H$4:$H$18<>"")))
C4:C9	C4	=SUM((--($A4=$F$4:$F$18))*(--($H$4:$H$18="")))

Good day awoohaw. Thank you for responding. Fluff had the answer that worked. I really appreciate the fact that you took the time to do an example and I will try out using the SUM function. That's when I had not considered. I always learn something in this forum.
Thanks again,
Dan Wilson...

 

[Fluff]

How about

=COUNTIFS('50-69'!$E:$E,$A2,'50-69'!$D:$D,B$1)

 

[Dan Wilson]

How about

=COUNTIFS('50-69'!$E:$E,$A2,'50-69'!$D:$D,B$1)

Good day fluff. Absolutely amazing! You did it again. I did change the B$1 at the end of the formula to D$1 so that it would search for the year in question. I thought at first that it was wrong as everything came up zeroes. Then I figured out that I had put the years into the COUNTS worksheet as 2 digit numbers while the data in the 59-60 worksheet was 4 digit numbers. That fixed the problem. I then extrapolated the formulas to work in all 150 Rows and filling the Columns for each year. I was then able to use the SUM function at the bottom to verify that all the songs had been searched. Thank you again. Your fix was RIGHT ON...

 

[Fluff]

You're welcome & thanks for the feedback.