MID and SEARCH Text Functions Inside SUMIFS plus List Data Validation

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

Posted in Blog.