Welcome to Excel Jet Consult blog post. In this Excel tutorial, we learn how to use MID & SEARCH Text Functions inside SUMIFS plus Data Validation to create a dynamic report. If you enjoy the video and tutorial, like, share, comment and subscribe to the YouTube channel.
Video
Sample Data
In the caption below, we have Sales Data formatted as an Excel Table and we have created named ranges in order not to alternate between the two sheet tabs
Extract and Sort Brand
In the Report Sheet, we want to extract all the unique brands and at the same time, sort them in ascending order. To do this, we will use Sort and Unique Dynamic Array Function.
- In cell A2, execute: =SORT(UNIQUE(Brand))
- Click CTRL + Enter
Brand is the recalled named range already created. Note, this tutorial was performed using Excel for Microsoft 365 which implies that the result spills to the neighboring cells below without using the traditional fill handle or auto-fill.
SUMIFS Calculation
Next, we want to write our formula that will return the Total Sales for Brand that are equals to 2014 with payment method that equals to Skrill which we need to technical extract from the value in cell B2. Therefore,
- In cell B2, execute the formula: =SUMIFS(Sales_Amount,Brand,A2#,Year,$B$15,Payment_Method,MID($B$1,7,SEARCH(” “,$B$1)-7))
- Click CTRL + Enter
Sales_Amount is the sum_range
Brand and A2# represent the criteria_range1 and spill range for criteria1 respectively
Year and $B$15 represent the criteria_range2 and criteria2 input values
Payment_Method represent the input value for criteria_range3
MID($B$1,7,SEARCH(” “,$B$1)-7) handles extraction of the Skrill located inside cell B2 which is the criteria3 input value. Kindly refer to the video for better explanation
Finally, we can see that we have accurate result as seen in the Sales Data filtered result in the Status Bar
