Combining TEXTBEFORE & TEXTAFTER Function with SUMIFS in Excel

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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *