Within the case of getting to sum based on the situation that the characters are the identical in a cell, what operate ought to be used? It is a very fascinating query that Be taught Excel On-line usually will get . Let’s learn how to do it on this case. Sum Non-Contiguous Cells In Excel 2003, 2007, 2010, 2013, 365, 2016
You might be viewing: sum non-contiguous cells in excel
For instance, now we have the next requirement:
Within the Worker ID column, we see the ID codes that include the characters KD1, KD2. However the positions of those characters aren’t the identical, i.e. it may be randomly situated in any place within the cell, so long as there may be the appropriate character.
The requirement is to calculate the full turnover of staff whose ID codes are KD1 and KD2 respectively.
sum by cells with the identical letter in Excel
Within the above drawback, it’s fairly sophisticated to learn at first. However absolutely you can be shocked when now we have many options:
Associated Keys: Sum alternating cells in Excel, sum related codes in Excel, SUMPRODUCT operate sum a number of circumstances, Sum equally spaced cells in Excel, sum horizontal rows in Excel, Perform to sum parts in Excel, Filter and sum in Excel, Method to calculate complete wage in Excel,
Technique 1: Create an additional column to acknowledge the worth calculation situation
As a result of the characters are combined within the character string in every cell, we will use the operate to find out every character individually to see which cell incorporates that character. Insert an additional column after the Income column and use the SEARCH operate to find out the next:
Within the assertion IFERROR(SEARCH($C$1,A2),0) now we have:
First: Seek for the worth in cell C1 (which is KD1) to see whether it is in cell A2. The return outcome, if any, would be the place of the character beginning to seem in cell A2. In any other case, the #VALUE2 error can be returned: Mix the IFERROR operate to keep away from the #VALUE error case when it’s not discovered by the SEARCH operate, then the error worth can be changed by zero.
The identical applies to the worth in D1 which is KD2
Then, the outcome > 0 means there’s a worth to search for, =0 is not any worth to search for.
Now the issue turns into fairly easy, we simply want to make use of the SUMIF operate to calculate the outcome as follows:
The results of complete turnover of staff with ID is KD1 =SUMIF(C2:C10,”>0″,B2:B10)
Equally, worker with KD2 code is =SUMIF(D2:D10,”>0″,B2:B10)
Technique 2: Direct calculation with out further columns with SUMIF . operate
Including an additional column makes us have so as to add 1 column every situation, at every column use many formulation. Why not do it instantly to avoid wasting assets of Excel? The reply is that it may be carried out instantly sure.
See additionally: Directions to repair the error of opening the pc with out with the ability to enter Home windows
write the components as follows:
With situation is KD1, now we have:
H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)
The sphere situation is in column A, phrase A2:A10 is the worker IDThe situation is “*”&F3&”*” which implies add 2 * earlier than and after the worth in cell F3. Since * is a particular character concatenated with cell F3, ought to enclose it in double quotes, then use & to concatenate the character. The whole space is the Income column, from B2:B10
The outcome is identical as the primary technique.
If you do not need to make use of the SUMIF operate, you’ll be able to exchange it with the SUMIFS operate as follows:
H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)
The contents on this operate are nonetheless the identical SUMIF operate, however the order is barely modified when the full space is upped to the first ingredient within the operate.
Technique 3: Use the SUMPRODUCT . operate
You should have heard of the SUMPRODUCT operate that may utterly exchange the SUMIF, SUMIFS operate. On this instance we’ll see if the SUMPRODUCT operate can be utilized
The components in cell I3 utilizing the SUMPRODUCT operate is as follows:
=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))
You’ll be able to see that the half IFERROR(SEARCH(F3,$A$2:$A$10,0) is the content material within the further column that we carried out above.
When put within the SUMPRODUCT operate, we’ll evaluate the outcome is it >0 or not, and with values >0 will use to calculate the full within the Income column.
* Word:
The next means of writing would not work:
=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)
Very fascinating, proper. So Studying Excel On-line has launched you to three methods to do that math drawback. Do you’ve another means? Please share with us.
As well as, you’ll be able to check with some extra articles on the identical subject:
Why use the SUMIFS operate as a substitute of the SUMPRODUCT operate to sum by a number of circumstances
Hopefully with the above helpful sharing, it can allow you to to proficiently use complete calculation in excel appropriately on your comfort. Particularly, for workplace staff who concentrate on working with numbers, knowledge must be understood, saving time and excessive work effectivity. Do not forget to replace extra new abilities!
Extra about this supply textSource textual content required for added translation data
Ship suggestions
Aspect panels
