Formula to count media in market

Kaitlyn Carroll

Hello,

I'm looking for a formula to count media based on their date range.

1) Count media where date range (start & end date columns) are in the PAST

2) Count media where date range (start & end date columns) include TODAY

3) Count media where date range (start & end date columns) are in the FUTURE

I believe I have 1 & 3 down, and I really just need help with #2.

Any ideas?

Thanks!

Nick Korna

Hi @Kaitlyn Carroll,

I think the following does what you're looking for with #2:

=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date],

Sample data:

I hope this is correct - if I've misunderstood something then let me know!

David Tutwiler

There are a few ways to do this, 1 being in reports. You can actually point a report at a sheet and use the filters to get the date ranges you want, then use the Group and Summary options to return live counts of your media elements.

To do this as a metric or with formulas you would need to do something like the formula below. Also, I am assuming that you require BOTH the start & end dates to include TODAY, but if that is different you might need to modify the formula slightly.

=COUNTIFS(Start:Start, TODAY(), End:End, TODAY())

This formula will count each entry where the Start date and End date for an entry is equal to TODAY().

Kaitlyn Carroll

Hi there!

The date would need to be either the start or the end date, or in between those dates.

So, if we had a billboard up from February 1, 2023 until April1, 2023, I would count that as a piece of media currently in market. So the start / end date won't always be today, but would be in between the date range.

Is there a formula for something like that?

Nick Korna

Hi @Kaitlyn Carroll,

I think the following does what you're looking for with #2:

=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date],

Sample data:

I hope this is correct - if I've misunderstood something then let me know!

Kaitlyn Carroll

I believe this solves it @Nick Korna ! Thanks!