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