 # SUMPRODUCT and FIND Functions to Sum Case Sensitive Values

Introduction

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to extract numbers from alphanumeric values across multiple columns and then summed. We will combination of several high-level Excel functions spanning over 600 length of characters! Let’s get started

Sample Data

In the caption below, we have our sample data in columns A and B. From the dataset, we the following numbers in cell A2: 1255003 and 790990 in cell B2 which we want to first extract and summed List of Functions

For this exercise, we will use the following functions

• LEN Function
• INDIRECT Function
• ROW Function
• MID Function
• ISNUMBER Function
• IF Function
• LARGE Function
• POWER Function
• SUMPRODUCT Function

Formula

• In cell C2, execute the formula:

=SUMPRODUCT(SUMPRODUCT(IFERROR(MID(A2,LARGE(IF(ISNUMBER(1+MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)),ROW(INDIRECT(“1:”&LEN(A2))),0),ROW(INDIRECT(“1:”&LEN(A2)))),1),0)*POWER(10,ROW(INDIRECT(“1:”&LEN(A2)))-1)))+(SUMPRODUCT(IFERROR(MID(B2,LARGE(IF(ISNUMBER(1+MID(B2,ROW(INDIRECT(“1:”&LEN(B2))),1)),ROW(INDIRECT(“1:”&LEN(B2))),0),ROW(INDIRECT(“1:”&LEN(B2)))),1),0)*POWER(10,ROW(INDIRECT(“1:”&LEN(B2)))-1)))

• Click Enter and copy down the formula

Kindly refer to the video for the definitive step by step explanation of each component of the functions

From the caption below, the summed of the extracted numeric values across the two columns is \$7,045,993 Conclusion

Although, this is quite a long and complex formula of about 600 characters long, it is worth knowing that it is possible to extract numbers from alphanumeric values.

Kindly refer to the video for more explanation

Posted in Blog and tagged .