Dynamic Yearly Trend of Sales Volume by Month and Product Category

Objective

To create an Excel formula that allows users to analyze sales volume trends over a year, breaking down data into monthly segments by both product category and sub-category.

Prerequisites

Ensure the following data structure is available in your Excel sheet:

• SalesData Table
  • Date: (e.g., A2:A1000) – Date of the sale
  • Category: (e.g., B2:B1000) – Product category
  • Sub-Category: (e.g., C2:C1000) – Product sub-category
  • Volume: (e.g., D2:D1000) – Sales volume

Formula Overview

To create a dynamic table that summarizes yearly sales trends by month, utilize the SUMIFS function. This formula will accumulate sales volumes based on selected criteria.

Formula Structure

=SUMIFS(SalesData!D:D, SalesData!A:A, ">="&EOMONTH(TODAY(),-12)+1, SalesData!A:A, "<="&EOMONTH(TODAY(),0), SalesData!B:B, "", SalesData!C:C, "")

Explanation of the Formula Components

1. SUMIFS: This function adds up all the volumes that meet specified criteria.

2. SalesData!D:D: This is the range containing sales volumes that we want to sum.

3. SalesData!A:A: This is the date column from which we filter our data.

4. Criteria for Dates:

   • ">="&EOMONTH(TODAY(),-12)+1: This criteria ensures that we get data from the beginning of the last year (12 months back).
   • "<="&EOMONTH(TODAY(),0): This criteria includes data until the current month.

5. and : These placeholders can be replaced with specific categories and sub-categories or can be linked to cells where you can dynamically enter selections.

Practical Example

Data Setup

Assuming your data is structured as follows:

Example Calculation

To calculate the sales volume for the Electronics category and Mobile sub-category for the past year, the formula would look like this when applied within a pivot or structured summary table:

=SUMIFS(SalesData!D:D, SalesData!A:A, ">="&EOMONTH(TODAY(),-12)+1, SalesData!A:A, "<="&EOMONTH(TODAY(),0), SalesData!B:B, "Electronics", SalesData!C:C, "Mobile")

This formula will yield a result of 150 for the example data provided, as it captures the only sales volume logged under that category and sub-category.

Dynamic Interaction

To make this formula dynamic:

• Use data validation lists or drop-down menus for category and sub-category selections.
• Link the <Category> and <Sub-Category> placeholders to these selections.

Conclusion

The provided formula and methodology allow users to efficiently analyze sales trends over a year by category and sub-category. The dynamic nature of the formula supports real-time analysis as new sales data is entered.

For further mastery in Excel data analysis and advanced techniques, consider exploring courses available on the Enterprise DNA Platform to enhance your skills.