Table of Contents
Google Sheets is a powerful tool that allows users to create and manage spreadsheets online. One useful feature of Google Sheets is the ability to sum cells only if they are not blank. This can be achieved by using the SUMIF function, which allows users to specify certain criteria for the cells they want to be included in the sum. By setting the criteria to only include non-blank cells, users can easily calculate the total of a specific range of cells without including any empty cells. This feature can be particularly useful when working with large datasets, as it saves time and avoids errors by automatically excluding blank cells from the calculation.
Google Sheets: Sum Cells If Not Blank
You can use the following basic formula to sum cells in a range that are not blank in Google Sheets:
=SUMIFS(sum_range, criteria_range, "<>")
This formula sums the values in the sum_range where the corresponding values in the criteria_range are not blank.
The following example shows how to use this formula in practice:
Example: SUMIF Not Blank in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the points scored by 10 different basketball players:
We can use the following formula to sum the values in the Points column only if the corresponding value in the Player column is not blank:
=SUMIFS(B2:B11, A2:A11, "<>")
The following screenshot shows how to use this formula in practice:
The sum of the points in column B where the player in column A is not blank is 45.
We can manually verify this by taking the sum of the points where the player value is not blank:
- Sum of Points: 4 + 5 + 5 + 10 + 10 + 5 + 6 = 45
Note that we could also use the following formula to only sum the points values where the player name is blank:
=SUMIFS(B2:B11, A2:A11, "")
The following screenshot shows how to use this formula in practice:
The sum of the points in column B where the player in column A is blank is 25.
We can also manually verify this by taking the sum of the points where the player value is blank:
- Sum of Points: 8 + 12 + 5 = 25
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I use Google Sheets to sum cells only if they are not blank?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-sum-cells-only-if-they-are-not-blank/
stats writer. "How can I use Google Sheets to sum cells only if they are not blank?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-sum-cells-only-if-they-are-not-blank/.
stats writer. "How can I use Google Sheets to sum cells only if they are not blank?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-sum-cells-only-if-they-are-not-blank/.
stats writer (2024) 'How can I use Google Sheets to sum cells only if they are not blank?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-sum-cells-only-if-they-are-not-blank/.
[1] stats writer, "How can I use Google Sheets to sum cells only if they are not blank?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use Google Sheets to sum cells only if they are not blank?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.