Table of Contents
The Excel SUMIF function is a useful tool for adding cells that contain specific text. This function allows users to specify certain criteria, such as text, and only add cells that meet that criteria. To use the SUMIF function, simply enter the range of cells to be added, followed by the criteria in quotation marks, and then the range of cells to be added if the criteria is met. This function can be particularly helpful when dealing with large datasets and wanting to quickly add only certain cells that contain specific text. It is a valuable feature for organizing and analyzing data in Excel.
Excel: Sum If Cells Contain Text
You can use the following methods to sum cells in Excel that contain specific text:
Method 1: Sum Cells that Contain One Specific Text
=SUMIF(A2:A11, "*avs*", B2:B11)
This formula will sum the cells in the range B2:B11 where the corresponding cells in A2:A11 contain “avs” in the cell.
Method 2: Sum Cells that Contain One of Several Text
=SUMIF(A2:A11, "*avs*", B2:B11)+SUMIF(A2:A11, "*eat*", B2:B11)
This formula will sum the cells in the range B2:B11 where the corresponding cells in A2:A11 contain “avs” or “eat” in the cell.
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Sum Cells that Contain One Specific Text
We can use the following formula to sum the values in the Points column where the Team name contains “avs” in the name:
=SUMIF(A2:A11, "*avs*", B2:B11)
The following screenshot shows how to use this formula in practice:
We can see that the cells where the Team column contains “avs” scored a total of 52 points.
We can manually verify this by summing the values where the Team column contains “avs”:
Sum of Points for Teams with “avs” in name: 12 + 5 + 8 + 10 + 17 = 52.
Example 2: Sum Cells that Contain One of Several Text
We can use the following formula to sum the values in the Points column where the Team name contains “avs” or “eat” in the name:
=SUMIF(A2:A11, "*avs*", B2:B11)+SUMIF(A2:A11, "*eat*", B2:B11)
The following screenshot shows how to use this formula in practice:
We can see that the cells where the Team column contains “avs” or “eat” scored a total of 65 points.
We can manually verify this by summing the values where the Team column contains “avs” or “eat”:
Sum of Points for Teams with “avs” or “eat” in name: 12 + 5 + 8 + 10 + 13 + 17 = 65.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I use the Excel SUMIF function to add cells that contain specific text?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-use-the-excel-sumif-function-to-add-cells-that-contain-specific-text/
stats writer. "How do I use the Excel SUMIF function to add cells that contain specific text?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-use-the-excel-sumif-function-to-add-cells-that-contain-specific-text/.
stats writer. "How do I use the Excel SUMIF function to add cells that contain specific text?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-use-the-excel-sumif-function-to-add-cells-that-contain-specific-text/.
stats writer (2024) 'How do I use the Excel SUMIF function to add cells that contain specific text?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-use-the-excel-sumif-function-to-add-cells-that-contain-specific-text/.
[1] stats writer, "How do I use the Excel SUMIF function to add cells that contain specific text?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I use the Excel SUMIF function to add cells that contain specific text?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.