Active Subscription Counter in Excel

Excel Formula for Active Subscriptions

This document outlines how to create an Excel formula that calculates the number of clients with an active subscription during a specified month, considering that the subscription end date may be empty.

Problem Statement

You have a list of clients with the following columns:

• Start Date: The date when the subscription begins.
• End Date: The date when the subscription ends (can be empty).

You need to determine how many clients had an active subscription in a specific month.

Formula Explanation

To calculate the number of clients with an active subscription in a particular month, you can use the following Excel formula:

=COUNTIFS(Start_Date_Range, "<=" & EOMONTH(Target_Month, 0), End_Date_Range, ">=" & EOMONTH(Target_Month, -1) + 1)

Components of the Formula:

1. Start_Date_Range: The range of cells containing the subscription start dates (e.g., A2:A100).
2. End_Date_Range: The range of cells containing the subscription end dates (e.g., B2:B100).
3. Target_Month: A cell reference containing a date from the month you are checking (e.g., C1).
4. EOMONTH() function:
   • EOMONTH(Target_Month, 0): Returns the last day of the target month.
   • EOMONTH(Target_Month, -1) + 1: Returns the first day of the target month.

Logic:

• Start dates must be on or before the last day of the target month.
• End dates must be on or after the first day of the target month or be empty (indicating the subscription is still active).

Practical Example

Assume you have the following data:

And you want to check how many clients had an active subscription in March 2023.

• Target_Month is set to a cell (e.g., 2023-03-01 in cell C1).

Using the formula:

=COUNTIFS(A2:A5, "<=" & EOMONTH(C1, 0), B2:B5, ">=" & EOMONTH(C1, -1) + 1)

This formula will evaluate as follows:

• Client A: Active through March (Active).
• Client B: Active as there is no end date (Active).
• Client C: Not active since end date is before March (Not Active).
• Client D: Active through March (Active).

The result will be 3, indicating that there are three clients with active subscriptions in March 2023.

Conclusion

The provided formula allows for a seamless calculation of active subscriptions based on start and end dates, accommodating scenarios where the end date may be empty, thereby representing ongoing subscriptions.

For further advanced training and resources, consider exploring the Enterprise DNA Platform, which offers courses that can enhance your proficiency in Excel and data analysis.