Existence of a TV Show over time???

E

east3rd

New Member
Joined
Sep 8, 2008
Messages
48
I have a tough one here! I am trying to calculate how many TV shows we had on our platform for each month of the year. My data consists of a title, license window start, license window end, and the content provider. For example:

titlelicensing_window_startlicensing_window_endprovider_id
Show #1
1/1/2020​
7/1/2020​
TV Creator 1

If I just create a pivot, the resulting table will only show me that I have 1 title in Jan 2020 and/or 1 title in July 2020. But what I need to know is how many titles I had for any given month for all the months in between. In the case above I want to know that this title "existed" in Jan, Feb, Mar, Apr... Solving this is beyond my skills so any thoughts or suggestions would be great.
Thank you

PS - here's some sample data to work with

titlelicensing_window_startlicensing_window_endprovider_id
Show #1
9/5/2020​
7/1/2021​
TV Creator 1
Show #2
8/29/2020​
7/1/2021​
TV Creator 2
Show #3
8/22/2020​
7/1/2021​
TV Creator 3
Show #4
8/15/2020​
7/1/2021​
TV Creator 1
Show #5
8/8/2020​
7/1/2021​
TV Creator 2
Show #6
8/1/2020​
7/1/2021​
TV Creator 3
Show #7
7/25/2020​
7/1/2021​
TV Creator 1
Show #8
7/18/2020​
7/1/2021​
TV Creator 2
Show #9
7/11/2020​
7/1/2021​
TV Creator 3
Show #10
5/23/2020​
4/2/2021​
TV Creator 1
Show #11
5/16/2020​
4/2/2021​
TV Creator 2
Show #12
5/9/2020​
4/2/2021​
TV Creator 3
Show #13
5/2/2020​
4/2/2021​
TV Creator 1
Show #14
4/25/2020​
4/2/2021​
TV Creator 2
Show #15
4/18/2020​
4/2/2021​
TV Creator 3
Show #16
4/11/2020​
4/2/2021​
TV Creator 1
Show #17
4/5/2020​
1/1/2021​
TV Creator 2
Show #18
4/5/2020​
9/1/2020​
TV Creator 3
Show #19
3/29/2020​
9/1/2020​
TV Creator 1
Show #20
3/29/2020​
1/1/2021​
TV Creator 2
Show #21
3/22/2020​
9/1/2020​
TV Creator 3
Show #22
3/22/2020​
1/1/2021​
TV Creator 1
Show #23
3/15/2020​
1/1/2021​
TV Creator 2
Show #24
3/15/2020​
1/1/2021​
TV Creator 3
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
A​
B​
C​
D​
E​
F​
G​
1​
titlelicensing_window_startlicensing_window_endprovider_idMonth
Total​
2​
Show #1
9/5/2020?​
7/1/2021?​
TV Creator 1January
0​
=COUNTIF(B:B,"1*")​
3​
Show #2
8/29/2020?​
7/1/2021?​
TV Creator 2February
0​
=COUNTIF(B:B,"2*")
4​
Show #3
8/22/2020?​
7/1/2021?​
TV Creator 3March
6​
=COUNTIF(B:B,"3*")
5​
Show #4
8/15/2020?​
7/1/2021?​
TV Creator 1April
5​
6​
Show #5
8/8/2020?​
7/1/2021?​
TV Creator 2May
4​
7​
Show #6
8/1/2020?​
7/1/2021?​
TV Creator 3June
0​
8​
Show #7
7/25/2020?​
7/1/2021?​
TV Creator 1July
3​
9​
Show #8
7/18/2020?​
7/1/2021?​
TV Creator 2August
5​
10​
Show #9
7/11/2020?​
7/1/2021?​
TV Creator 3September
1​
11​
Show #10
5/23/2020?​
4/2/2021?​
TV Creator 1Octover
0​
12​
Show #11
5/16/2020?​
4/2/2021?​
TV Creator 2November
0​
13​
Show #12
5/9/2020?​
4/2/2021?​
TV Creator 3December
0​
14​
Show #13
5/2/2020?​
4/2/2021?​
TV Creator 1
​
15​
Show #14
4/25/2020?​
4/2/2021?​
TV Creator 2
​
16​
Show #15
4/18/2020?​
4/2/2021?​
TV Creator 3
​
17​
Show #16
4/11/2020?​
4/2/2021?​
TV Creator 1
​
18​
Show #17
4/5/2020?​
1/1/2021?​
TV Creator 2
​
19​
Show #18
4/5/2020?​
9/1/2020?​
TV Creator 3
​
20​
Show #19
3/29/2020?​
9/1/2020?​
TV Creator 1
​
21​
Show #20
3/29/2020?​
1/1/2021?​
TV Creator 2
​
22​
Show #21
3/22/2020?​
9/1/2020?​
TV Creator 3
​
23​
Show #22
3/22/2020?​
1/1/2021?​
TV Creator 1
​
24​
Show #23
3/15/2020?​
1/1/2021?​
TV Creator 2
​
25​
Show #24
3/15/2020?​
1/1/2021?​
TV Creator 3
​
 
Upvote 0
Thanks for the response, but this doesn't answer my question. It only tallies the number of shows that have start dates for a given month. I can do that in a pivot. What I need is a way of tallying the fact that Show #1 appeared in Sept 2020, Oct 2020, Nov 2020 etc. etc. This is what I'm having an issue figuring out.
 
Upvote 0
Well ... there is only one of each Show shown in Column A.

There are repetitive TV Creators in Column D.

Which Column are we targeting ?
 
Upvote 0
I'm targeting column 'A' - in essence, I have a large library of shows with varying start and end dates. This means that any given show could be available across multiple months, but since I only have start/end dates I can't seem to create a snapshot of which shows were available in August 2020 (for example). Put another way, if none of the shows in my list started or ended in August, then a standard pivot (or the solution you provided) would state August = 0, when in fact there were shows that were available in that month. I hope that makes sense - hard to explain in text!
 
Upvote 0
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 
Upvote 0
Not entirely sure what you are after, but do either of these do what you want?

+Fluff New.xlsm
ABCDEFGHI
1titlelicensing_window_startlicensing_window_endprovider_id01/10/2020
2Show #105/09/202001/07/2021TV Creator 1Show #101/03/20206
3Show #229/08/202001/07/2021TV Creator 2Show #201/04/202011
4Show #322/08/202001/07/2021TV Creator 3Show #301/05/202015
5Show #415/08/202001/07/2021TV Creator 1Show #401/06/202015
6Show #508/08/202001/07/2021TV Creator 2Show #501/07/202018
7Show #601/08/202001/07/2021TV Creator 3Show #601/08/202023
8Show #725/07/202001/07/2021TV Creator 1Show #701/09/202024
9Show #818/07/202001/07/2021TV Creator 2Show #801/10/202021
10Show #911/07/202001/07/2021TV Creator 3Show #901/11/202021
11Show #1023/05/202002/04/2021TV Creator 1Show #1001/12/202021
12Show #1116/05/202002/04/2021TV Creator 2Show #1101/01/202121
13Show #1209/05/202002/04/2021TV Creator 3Show #1201/02/202116
14Show #1302/05/202002/04/2021TV Creator 1Show #13
15Show #1425/04/202002/04/2021TV Creator 2Show #14
16Show #1518/04/202002/04/2021TV Creator 3Show #15
17Show #1611/04/202002/04/2021TV Creator 1Show #16
18Show #1705/04/202001/01/2021TV Creator 2Show #17
19Show #1805/04/202001/09/2020TV Creator 3Show #20
20Show #1929/03/202001/09/2020TV Creator 1Show #22
21Show #2029/03/202001/01/2021TV Creator 2Show #23
22Show #2122/03/202001/09/2020TV Creator 3Show #24
23Show #2222/03/202001/01/2021TV Creator 1
24Show #2315/03/202001/01/2021TV Creator 2
25Show #2415/03/202001/01/2021TV Creator 3
Data
Cell Formulas
RangeFormula
F2:F22F2=FILTER(A2:A25,(B2:B25<=EOMONTH(F1,0))*(C2:C25>=F1))
I2:I13I2=COUNT(FILTER(ROW($A$2:$A$25),($B$2:$B$25<=EOMONTH(H2,0))*($C$2:$C$25>=H2)))
Dynamic array formulas.
 
Upvote 0
Here's a way without using the new array functions:

Book1
ABCDEFGH
1titlelicensing_window_startlicensing_window_endprovider_idMonthShows
2Show #19/5/20207/1/2021TV Creator 11/1/20200
3Show #28/29/20207/1/2021TV Creator 22/1/20200
4Show #38/22/20207/1/2021TV Creator 33/1/20200
5Show #48/15/20207/1/2021TV Creator 14/1/20206
6Show #58/8/20207/1/2021TV Creator 25/1/202011
7Show #68/1/20207/1/2021TV Creator 36/1/202015
8Show #77/25/20207/1/2021TV Creator 17/1/202015
9Show #87/18/20207/1/2021TV Creator 28/1/202019
10Show #97/11/20207/1/2021TV Creator 39/1/202023
11Show #105/23/20204/2/2021TV Creator 110/1/202021
12Show #115/16/20204/2/2021TV Creator 211/1/202021
13Show #125/9/20204/2/2021TV Creator 312/1/202021
14Show #135/2/20204/2/2021TV Creator 11/1/202121
15Show #144/25/20204/2/2021TV Creator 22/1/202116
16Show #154/18/20204/2/2021TV Creator 33/1/202116
17Show #164/11/20204/2/2021TV Creator 14/1/202116
18Show #174/5/20201/1/2021TV Creator 25/1/20219
19Show #184/5/20209/1/2020TV Creator 36/1/20219
20Show #193/29/20209/1/2020TV Creator 17/1/20219
21Show #203/29/20201/1/2021TV Creator 28/1/20210
22Show #213/22/20209/1/2020TV Creator 39/1/20210
23Show #223/22/20201/1/2021TV Creator 110/1/20210
24Show #233/15/20201/1/2021TV Creator 211/1/20210
25Show #243/15/20201/1/2021TV Creator 312/1/20210
261/1/20220
Sheet17
Cell Formulas
RangeFormula
H2:H26H2=COUNTIFS(B$2:B$25,"<="&G2,C$2:C$25,">="&G2)


Note that this doesn't count partial months. For example, there are several shows (#19-24) starting in March, but H4 = 0, because it wasn't a full month. We can adapt the formula to use EOMONTH or something if you want to include partial months.
 
Upvote 0
Thanks everyone for the responses - I'm away from my desk at the moment but will let you know where I land tomorrow.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
139
Peter_SSs
Peter_SSs
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
89
RoryA
RoryA
H
  • Solved
Transpose data.
Replies
4
Views
105
harzer
H
D
  • Solved
Returning the unique label whether a date falls within a list of date ranges that have all been uniquely labeled.
Replies
2
Views
362
drmurph
D
U
  • Solved
Week number in the calendar
Replies
2
Views
802
UmairKamal
U

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,060
Latest member
mtsheetz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top