How to use SUMIF text in Excel
A free Office suite fully compatible with Microsoft Office
A free Office suite fully compatible with Microsoft Office
The SUMIF function is conditional if the method used to total the cells is dependent on specific criteria, not just specific text. As an illustration, let's sum a set of cells. The function =SUMIF(Text Range,Text, Cells range for sum) may be used to determine if the following cell has the provided text.
For instance, suppose we receive information about a company's sales and need to figure out the overall revenues or need to add up names with particular text and student test scores. In this case, we may sum up data based on criteria by using Excel's SUMIF function.
In Excel, the Sumif function is used to calculate the sum of values in a cell range when another set of cell ranges or a similar array meets certain requirements. The method can also be used to add cells with particular or incomplete text.
The following parameters are included in the syntax of the SUMIF function:
Range: It's necessary as It displays the range or values of the cells that must be compared to the given criteria.
Criteria:. It indicates the requirement that has to be verified or tested against each value in the given range.
sum range: It's not required. If the parameter range meets the specified criterion or criteria, it reflects the values or range of cells that must be combined together. Excel sums the cells indicated in the range argument itself if this is not provided in the function.
Case # 01
Let's assume that we have the test results for two classes or sections, A and B. We need to know the kids' entire test A results.
Students' grades are kept in column C, while the student section is kept in column B. Then, the formula below instructs Excel to provide the total score for all students in section A as a sum:
=SUMIF(B2:B11,”A”,C2:C11)
The function would be described as follows:
So, as we can see in the picture up above, a straightforward SUMIF suffices to provide results depending on a certain text condition. The method adds together all of the scores when A is assigned to the relevant area.
As a result, here is the outcome:
As their corresponding section is A, the scores that are noted below will be summed to get a final score of 466.
Case # 02
Now, in the previous example, there is an additional column that indicates if a student's score is Excellent,”, Bad, or Average. Therefore, we are looking for students' overall scores that have Average scores:
Students' grades are kept in column C, while an identifier (such as Bad or Average) is kept in column D. Then, the formula below instructs Excel to provide the sum of all students' scores with the label Average:
=SUMIF(D2:D11,”Average”,C2:C11)
The function would be described as follows:
As a result, here is the outcome:
Therefore, the formula totals all of the scores where the matching identifier is Average, as can be seen in the screenshot up above.
Case # 03:
Some of our workers have team names and wages listed. Mg, “RIT” and Able are the three companies. Now, we're interested in learning the overall salary of these companies. To check if the team name starts with Mg in this instance, we utilise the wildcard * in the SUMIF function criteria:
=SUMIF(B2:B7,”Mg*”,C2:C8)
Below is how we would define the function:
So, as we can see in the picture up above, the formula adds up all the wages where the matching team names start with Mg, and to do this, * is utilised as a wildcard in the text condition (up top).
Thus, the outcome is as follows:
So, Did you learn about how to use SUMIF text in an Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.
You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience
Was this helpful?
Yes
No
Trending Tutorials
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to color cell based on value in Excel?
- 5. How to compare two excel sheets and highlight differences
- 6. How to add text to beginning or end of all cells in Excel