Table of Contents
In order to calculate the sum of cells in Excel only if they are not blank, you can use the SUMIF function. This function allows you to specify a range of cells to be evaluated and a criteria to determine which cells should be included in the calculation. By setting the criteria to “not blank” or “”, the function will only add up the values of cells that are not empty. This ensures that the final sum will only include the relevant and non-blank cells. This can be a useful tool for organizing and analyzing data in Excel.
Excel: Sum If Not Blank
You can use the following formulas in Excel to sum a range of cells if the value in a corresponding range is not blank:
Formula 1: Sum If Not Blank (One Column)
=SUMIF(A:A, "<>", B:B)
This formula sums the values in column B only where the values in column A are not blank.
Formula 2: Sum If Not Blank (Multiple Columns)
=SUMIFS(C:C, A:A, "<>", B:B, "<>")
This formula sums the values in column C only where the values in column A and B are not blank.
The following examples show how to use each formula in practice.
Example 1: Sum If Not Blank (One Column)
The following screenshot shows how to sum the values in the Points column only where the values in the Team column are not blank:
The sum of the values in the Points column for the rows where Team is not blank is 138.
We can verify this is correct by manually calculating the sum of the points for the teams that are not blank:
Sum of points: 22 + 17 + 28 + 30 + 12 + 11 + 18 = 138.
This matches the value that we calculated using the formula.
Example 2: Sum If Not Blank (Multiple Columns)
The following screenshot shows how to sum the values in the Points column only where the values in the ConferenceandTeam columns are not blank:
The sum of the values in the Points column for the rows where the ConferenceandTeam columns are not blank is 90.
We can verify this is correct by manually calculating the sum of the points where the conference and team is not blank:
Sum of points: 22 + 17 + 28 + 12 + 11 = 90.
This matches the value that we calculated using the formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I calculate the sum of cells in Excel only if they are not blank?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-cells-in-excel-only-if-they-are-not-blank/
stats writer. "How can I calculate the sum of cells in Excel only if they are not blank?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-cells-in-excel-only-if-they-are-not-blank/.
stats writer. "How can I calculate the sum of cells in Excel only if they are not blank?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-cells-in-excel-only-if-they-are-not-blank/.
stats writer (2024) 'How can I calculate the sum of cells in Excel only if they are not blank?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-of-cells-in-excel-only-if-they-are-not-blank/.
[1] stats writer, "How can I calculate the sum of cells in Excel only if they are not blank?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate the sum of cells in Excel only if they are not blank?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.