Table of Contents
This calculation method in Google Sheets allows users to find the average value of cells that meet a specific text criteria. By using the AVERAGEIF function, users can specify the range of cells to be evaluated and the criteria that must be met for those cells to be included in the calculation. This allows for a more precise and efficient way to calculate the average of data in a spreadsheet, saving time and effort for the user. By following the proper syntax and inputting the appropriate parameters, users can easily obtain the average of cells based on their desired criteria in Google Sheets.
Google Sheets: Calculate Average If Cell Contains Text
You can use the following formula to calculate the average in Google Sheets only for the cells that contain a specific text:
=AVERAGEIF(A2:A13,"*text*",B2:B13)
This particular formula will calculate the average of the values in the range B2:B13 only for the cells that contain “text” in the range A2:A13.
Note: The asterisks are wildcard characters that tell Google Sheets to ignore any text before or after a specific string.
The following example shows how to use this formula in practice.
Example: Calculate Average If Cell Contains Text
Suppose we have the following dataset that shows the points scored by 12 different basketball players:
We can use the following formula to calculate the average points scored by players on any team with “avs” in the team name:
=AVERAGEIF(A2:A13,"*avs*",B2:B13)
The following screenshot shows how to use this formula in practice:
The average points scored by players on a team with “avs” in the name is 22.75.
In this particular example, there are two team names with “avs” in the name: Mavs and Cavs.
We can manually calculated the average points scored by players on these teams:
Average Points Scored = (31 + 23 + 21 +16) / 4= 22.75.
This matches the value calculated by our formula.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I calculate the average of cells in Google Sheets based on specific text criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-in-google-sheets-based-on-specific-text-criteria/
stats writer. "How can I calculate the average of cells in Google Sheets based on specific text criteria?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-in-google-sheets-based-on-specific-text-criteria/.
stats writer. "How can I calculate the average of cells in Google Sheets based on specific text criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-in-google-sheets-based-on-specific-text-criteria/.
stats writer (2024) 'How can I calculate the average of cells in Google Sheets based on specific text criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-in-google-sheets-based-on-specific-text-criteria/.
[1] stats writer, "How can I calculate the average of cells in Google Sheets based on specific text criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate the average of cells in Google Sheets based on specific text criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.