In the case of having to sum according to the condition that the characters are the same in a cell, what function should be used? This is a very interesting question that Learn Excel Online often gets . Let’s find out how to do it in this case. How to Sum Non-Contiguous Cells In Excel 2003, 2007, 2010, 2013, 365, 2016
You are viewing: How to sum non-contiguous cells in excel
For example, we have the following requirement:
In the Employee ID column, we see the ID codes that contain the characters KD1, KD2. But the positions of these characters are not the same, i.e. it can be randomly located in any position in the cell, as long as there is the right character.
The requirement is to calculate the total turnover of employees whose ID codes are KD1 and KD2 respectively.
How to sum by cells with the same letter in Excel
In the above problem, it is quite complicated to read at first. But surely you will be surprised when we have many solutions:
Related Keys: Sum alternating cells in Excel, How to sum similar codes in Excel, SUMPRODUCT function sum multiple conditions, Sum equally spaced cells in Excel, How to sum horizontal rows in Excel, Function to sum elements in Excel, Filter and sum in Excel, Formula to calculate total salary in Excel,
Method 1: Create an extra column to recognize the value calculation condition
Because the characters are mixed in the character string in each cell, we can use the function to determine each character individually to see which cell contains that character. Insert an extra column after the Revenue column and use the SEARCH function to determine the following:
In the statement IFERROR(SEARCH($C$1,A2),0) we have:
First: Search for the value in cell C1 (which is KD1) to see if it is in cell A2. The return result, if any, will be the position of the character starting to appear in cell A2. Otherwise, the #VALUE2 error will be returned: Combine the IFERROR function to avoid the #VALUE error case when it is not found by the SEARCH function, then the error value will be replaced by zero.
The same applies to the value in D1 which is KD2
Then, the result > 0 means there is a value to look for, =0 is no value to look for.
Now the problem becomes quite simple, we just need to use the SUMIF function to calculate the result as follows:
The result of total turnover of employees with ID is KD1 =SUMIF(C2:C10,”>0″,B2:B10)
Similarly, employee with KD2 code is =SUMIF(D2:D10,”>0″,B2:B10)
Method 2: Direct calculation without extra columns with SUMIF . function
Adding an extra column makes us have to add 1 column each condition, at each column use many formulas. Why not do it directly to save resources of Excel? The answer is that it can be done directly yes.
See also: Instructions to fix the error of opening the computer without being able to enter Windows
How to write the formula as follows:
With condition is KD1, we have:
H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)
The field condition is in column A, word A2:A10 is the employee IDThe condition is “*”&F3&”*” which means add 2 * before and after the value in cell F3. Since * is a special character concatenated with cell F3, should enclose it in double quotes, then use & to concatenate the character. The total area is the Revenue column, from B2:B10
The result is the same as the first method.
If you do not want to use the SUMIF function, you can replace it with the SUMIFS function as follows:
H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)
The contents in this function are still the same SUMIF function, but the order is slightly changed when the total area is upped to the 1st element in the function.
Method 3: Use the SUMPRODUCT . function
You must have heard of the SUMPRODUCT function that can completely replace the SUMIF, SUMIFS function. In this example we will see if the SUMPRODUCT function can be used
The formula in cell I3 using the SUMPRODUCT function is as follows:
=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))
You can see that the part IFERROR(SEARCH(F3,$A$2:$A$10,0) is the content in the extra column that we done above.
When put in the SUMPRODUCT function, we will compare the result is it >0 or not, and with values >0 will use to calculate the total in the Revenue column.
* Note:
The following way of writing doesn’t work:
=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)
Very interesting, right. So Learning Excel Online has introduced you to 3 ways to do this math problem. Do you have any other way? Please share with us.
In addition, you can refer to some more articles on the same topic:
Why use the SUMIFS function instead of the SUMPRODUCT function to sum by multiple conditions
Hopefully with the above useful sharing, it will help you to proficiently use total calculation in excel correctly for your convenience. In particular, for office workers who specialize in working with numbers, data needs to be understood, saving time and high work efficiency. Don’t forget to update more new skills!
More about this source textSource text required for additional translation information
Send feedback
Side panels
