Dynamic Yearly Trend Sales Volume vs. Per Month Per Subcategory in Excel

Objective

To create a dynamic Excel formula that calculates the sales volume for each product subcategory on a monthly basis, allowing for year-to-year comparisons.

Assumptions

1. Sales data is organized in a table with the following columns:

   • Date: The date of the sale.
   • Sales Volume: The quantity sold.
   • Subcategory: The product subcategory.

2. The data is in the range A2:C1000 where:

   • Column A contains the dates.
   • Column B contains the sales volumes.
   • Column C contains the subcategories.

Formula Creation

Step 1: Define Dynamic Ranges

To make the formula dynamic, we will define the sales data range using the OFFSET function within named ranges or directly in the formula.

Step 2: Calculate Monthly Sales Volume by Subcategory

Use the following formula to get the total sales volume for a specific subcategory in a given month and year:

=SUMIFS(B:B, C:C, "Subcategory_Name", A:A, ">="&DATE(Year_Number, Month_Number, 1), A:A, "<="&EOMONTH(DATE(Year_Number, Month_Number, 1), 0))

Variables

• replace "Subcategory_Name" with the specific subcategory you want to analyze.
• replace Year_Number with the desired year, e.g., 2023.
• replace Month_Number with the desired month, e.g., 1 for January.

Explanation of the Formula

• SUMIFS: This function sums the values in B:B (Sales Volume) that meet multiple criteria.
• C:C, "Subcategory_Name": This checks that the products belong to the specified subcategory.
• A:A, ">="&DATE(Year_Number, Month_Number, 1): This checks that the date is equal to or greater than the first day of the specified month.
• A:A, "<="&EOMONTH(DATE(Year_Number, Month_Number, 1), 0): This checks that the date is equal to or less than the last day of the specified month.

Practical Example

Example Data

Example Formula

To calculate the sales volume of "Electronics" in January 2023:

=SUMIFS(B:B, C:C, "Electronics", A:A, ">="&DATE(2023, 1, 1), A:A, "<="&EOMONTH(DATE(2023, 1, 1), 0))

Result

The formula calculates and returns the total sales volume for the subcategory "Electronics" in January 2023.

Dynamic Updates

To further enhance the functionality:

• Use data validation to have dropdowns for Subcategory, Year, and Month to make this formula dynamic and user-friendly.
• Consider utilizing PivotTables for more complex analysis, as they can provide comprehensive insights with less manual formula management.

Conclusion

By implementing this formula, you will be able to effectively analyze monthly sales trends by subcategory dynamically. For further training and mastery in Excel and data analysis techniques, consider exploring the Enterprise DNA Platform, which provides valuable resources and insights into advanced data analysis methodologies.