Welcome to Excel Jet Consult blog post. In this tutorial, we going to learn how to combine TEXTBEFORE & TEXTAFTER Function with SUMIFS in Excel
Video
Sample Dataset for the First Example
In the screenshot below, we have the first sample dataset which contains Full Name. So, what we wanna do is to use the new TEXTBEFORE and the TEXTAFTER new text functions to extract the Middle Name
Extract Middle Name
To extract the middle name:
- In cell C2, execute the formula: =TEXTBEFORE(TEXTAFTER(Table2[@[Full Name]],” “),” “)
- Click Enter and copy down the formula
From the caption below, we can see that we have easily extracted the middle name
Sample Dataset for the Second Example
In the screenshot below, we have transaction dataset that spans from Date to Sales columns. In addition, the dataset in already formatted as an Excel Table which makes writing formula more efficient and neat!
In the screenshot below, we want to use the combination of the powerful SUMIFS Function with the TEXTBEFORE & the TEXTAFTER functions to specifically point to DogeCoin crypto currency type in cell A1
SUMIFS with TEXTBEFORE & TEXTAFTER FORMULA
- In cell B4, execute the formula: =SUMIFS(Data[Sales],Data[Product],A4#,Data[State],B3#,Data[Crypto Currency],TEXTBEFORE(TEXTAFTER(A1,” “),” “))
- Click Enter
From the screenshot below, we have successfuly calculated the Sales by Product, State & Crypto Currency type
