Introduction
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use advanced method to sum values using combination of MID, LEFT & FIND Text Functions. Let’s get started
Sample Data
In the caption below, we have sample data
We want calculate the Total for Account Manager whose First Name is David and Country of operation is Nigeria. Based on the sample data, we have some Account Managers in column A whose Last Name is David but First Name is not David i.e Hamid David in row 10.
In addition, the Country is located in the middle position in column B while First Name is located in the Leftmost position of column A.
Hence, we are going to use LEFT & FIND functions when working on Account Manager in Column A and MID & FIND functions for Product Country Region in column B
Calculation
- In cell I2, execute the formula:
=SUMPRODUCT((((MID(Product_Country_Region,FIND(” “,Product_Country_Region)+1,FIND(” “,Product_Country_Region,FIND(” “,Product_Country_Region)+1)-FIND(” “,Product_Country_Region)-1)=H2))*(LEFT(Account_Manager,FIND(” “,Account_Manager)-1)=G2))*Total)
- Click Enter
In the caption below, the Total for Account Manager whose First Name is David and Country equals to Nigeria is
| ₦6,197,336 |
That’s basically how to deploy the text functions inside SUMPRODUCT to create advanced and weird calculation in Excel