Microsoft Excel helps us to do simple and complex calculations easily. If there are a list of numbers, then it is very simple to find the SUM of them. But sometimes, we come across a situation where the numbers in cells are intermittent. This is called sporadic behavior in Excel. Did not get what I said? Let us say, we have few numbers in few cells and followed by a blank cell. You need to sum all the numbers and display the result in following blank cell. This is the way to calculate sporadic totals in Excel.
It is not an easy task and cannot be done easily if we have huge data in Excel sheet in this pattern. In this article, I will show you how to calculate sporadic totals in Excel easily which helps you to calculate the sum of cells which are intermittent.
Calculate Sporadic Totals in Excel
Here is the Excel sheet which I have showing Products and Sales columns. In ‘Sales’ column, you could see that there is a blank cell after few cells. We need to calculate the sum of cells and show the result in the followed blank cell, i.e., we need to calculate sporadic totals in Excel here.
So, first select all cells in ‘Sales’ column. Next press CTRL+G or F5 to open Go To dialog box. Click on ‘Special’ button and select ‘Constants’ radio button. Click ‘Ok,’ and it would select all cells in ‘Sales’ column which has numbers.
Next, go to ‘Home’ tab and under ‘Editing’ section click on ‘AutoSum’ option. This would sum up values in selected cells and displays the result in respective blank cells.
To highlight blank cells which have a sum of cells, select all cells in ‘Sales’ column again. Next press CTRL+G or F5 button again to open Go To dialog box. Now, click on ‘Special’ button and select ‘Formulas’ radio button and click ‘Ok’.
This would select cells which show totals which we calculated above as it has formulas. Fill it with color to differentiate with other cells.
This is the simple way to calculate sporadic totals in Excel. Hope you find it useful and if you have anything to add, please do mention us in comments.