Dynamic Cross Tab Report using LET, SUMIFS & Many More Functions [Single Cell Formula Series]

Welcome to Excel Jet Consult blog post. In this Excel tutorial, in continuation of our single cell formula series, we will learn how to create a dynamic cross tab report using LET, SUMIFS & Many More Functions. Let’s get started

Video

Sample Dataset

In the screenshot below, we’ve got a sales dataset which is officially stored as an Excel Table and named Data

Create List Data Validation Drop-down

To make the report to be dynamic, we are going to create a list data validation in cell K1 which will house all the unique sorted lost of the Order Priority column. However, before we create the dropdown, we’ve got to extract the unique list and optionaly sort from A to Z.

• In cell L1, execute the formula: =TOROW(SORT(UNIQUE(data[Order Priority])))
• Click Enter

From the screenshot below, we can see the unique sorted list of the Order_Priority in the row one

• By selecting cell K1, in the Data Tools group of the Data tab, click on Data Validation
• In the Allow dropdown of the Data Validation dialogue box, select List
• In the source box, select the first value in cell L1 and type in the # sign.

• Click OK. The dropdown is created!

Single Cell Formula

The core part of the blog post is to create a robust single cell formula that will display the Total Sales by Account Manager and Order Year. To do that:

• In cell 13, execute this giant formula: =LET(
  AccountManager,data[Account Manager],UniqueSortedAccountManager,SORT(UNIQUE(AccountManager)),
  OrderYear,data[Order Year],UniqueSortedOrderYear,SORT(UNIQUE(OrderYear)),
  OrderPriority,data[Order Priority],UniqueOrderPriority,K1,
  TotalSales,data[Total],
  UniqueSortedOrderYearToRow,TOROW(UniqueSortedOrderYear),
  AccountManagerHeader,data[[#Headers],[Account Manager]],
  OrderYearHeader,data[[#Headers],[Order Year]],
  TopRowCalc,HSTACK(CONCAT(AccountManagerHeader,” / “,OrderYearHeader),UniqueSortedOrderYearToRow),
  MiddleCalc,HSTACK(UniqueSortedAccountManager,SUMIFS(TotalSales,AccountManager,UniqueSortedAccountManager,OrderYear,UniqueSortedOrderYearToRow,OrderPriority,UniqueOrderPriority)),
  BottomRowCalc,HSTACK(“Total Sales”,SUMIFS(TotalSales,OrderYear,UniqueSortedOrderYearToRow,OrderPriority,UniqueOrderPriority)),
  finalCalc,VSTACK(TopRowCalc,MiddleCalc,BottomRowCalc),finalCalc)
• Click Enter.

Let’s break down the giant formula step by step to understand its purpose:

1. LET: The LET function allows you to define variables within a formula, making it easier to manage and understand complex calculations.
2. AccountManager: This variable references the data in the “Account Manager” column.
3. UniqueSortedAccountManager: This variable stores the unique and sorted list of account managers from the “Account Manager” column.
4. OrderYear: This variable references the data in the “Order Year” column.
5. UniqueSortedOrderYear: This variable stores the unique and sorted list of order years from the “Order Year” column.
6. OrderPriority: This variable references the value in cell K1, which is likely used as a filter for the order priority.
7. TotalSales: This variable references the data in the “Total” column.
8. UniqueSortedOrderYearToRow: This variable is a row number reference for the unique and sorted order years. It helps identify the row number for each unique order year.
9. AccountManagerHeader: This variable stores the header of the “Account Manager” column.
10. OrderYearHeader: This variable stores the header of the “Order Year” column.
11. TopRowCalc: This variable combines the headers of the “Account Manager” and “Order Year” columns and the row numbers of the unique sorted order years using the HSTACK function. This will likely create a table header that indicates the account managers and the order years they have data for.
12. MiddleCalc: This variable combines the unique sorted account manager names with the corresponding total sales for each account manager, filtered by the specific order priority and order year, using the SUMIFS function. It essentially calculates the total sales for each account manager based on the specified order priority and order year.
13. BottomRowCalc: This variable combines the label “Total Sales” with the total sales for all account managers, filtered by the specific order priority and order year, using the SUMIFS function. It calculates the overall total sales for the specified order priority and order year.
14. finalCalc: This variable stacks the previously calculated headers, account manager sales, and total sales into a single array using the VSTACK function.
15. The formula finally returns the finalCalc variable as the result of the entire expression.

To better understand every component of the giant formula, kindly watch the video above.

Voila! The screenshot below shows the Total Sales by Account Manager and Order Year

See you in the next video