Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use what has become my favorite function in Excel – SUMPRODUCT with rarely known Case Sensitive function – EXACT. I was inquisitive minded to discover whether it is possible to sum values based on text. That led me into about 20 minutes of trying to manipulate data that are case sensitive. Let’s dive into this discovery
Definition of Functions & Syntax
- SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
- Syntax: =SUMPRODUCT(array1, [array2], [array3],…)
- Array1 – represent the only compulsory argument whose component is required to multiply and subsequently add up. Array2 to 255 represent optional argument
- EXACT function compares two text strings and returns TRUE if they are the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
- Syntax: =EXACT(text1,text2)
- Text1 – represent the first text string
- Text2 – represent the second text string
Working Example & Solution
In the caption below, we have a sample data. In column A, we have a Customer named Connor Rulibeth (all in proper case) and another CONNOR RULIBETH (all in upper case). In addition, in column B, we have New York and NEW YORK State. The Connor Rulibeth and CONNOR RULIBETH and New York and New York are obviously not the same with regards to case sensitivity.
Note, the data is formatted as Excel Table and named: Data
Therefore, what we intend to do is to add up all the Total for CONNOR RULIBETH and NEW YORK only
In cell G2, execute the formula: =SUMPRODUCT(EXACT(Data[Customer Name],E2)*Data[Total])+SUMPRODUCT(EXACT(Data[States],F2)*Data[Total])
In the above formula, EXACT(Data[Customer Name],E2)*Data[Total] evaluates whether the value in E2 and Data[Customer Name] list is exactly the same. The first 5 Customer Name will return FALSE (not the same) while the sixth will return TRUE (CONNOR RULIBETH); and so on. Then, I multiple by the Total
Next, we have this SUMPRODUCT(EXACT(Data[States],F2)*Data[Total]) to check for States
In the caption below, we got ₦201,013.00. When we check the Status Bar based on the selected rows that satisfied the criteria, we have the same Total
This is really a cool discovery. I definitely know you’ve learnt something new. More powerful tutorials coming