When one wants to hide data from the view, excel has a filter option that filters data in the worksheet. This also data into the filtered and the visible. It may be easy to sum numbers in an excel column, and sometimes you may need the filter option to hide some data to meet your objectives.
When you want to perform calculations in these cells, e.g., the sum of function values, including hidden data, will be added. The sum function does not know what values you want to evaluate and which not to now what do you do to add up only the filtered or visible data?
Method 1: Use of subtotal function
This subtotal function method ignores rows that a filter has excluded, and now only visible cells can be added up. This is done as follows:
Come up with the list of the table as I have done: Make sure to hide some rows.
1. In a blank cell, C13, for example, enter this formula: =Subtotal(109,C2:C5) (109 indicates when you sum the numbers, the hidden values will be ignored; C2:C5 is the Range you will sum ignoring filtered rows.), and press the Enter key.
2. The sum of visible cells can be calculated using the formula above if the worksheet contains hidden rows. Ignoring cells in hidden columns cannot be worked out with the stated above formula.
Method 2: Use of user-defined functions
This works best with those who have gone much into the coding thing. Involves the use of visual basic.
1. Press the shortcut key alt+f11, which in turn will open the visual basic.
2. In the visual basic, go to "insert" then "Module" and have the following codes
Function SumVisible(WorkRng As Range) As Double
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
SumVisible = total
3. Save the code and enter formulae =SumVisible(C2:C5) on the empty cell
Method 3: Using kutoolcalculate, the sum of filtered cells
Usually, cells that are either filtered/hidden or not are counted in the specified Range using the SUM/Count/Average function. While ignoring hidden rows can only enable either summing or counting, or averaging. However, Kutools for Excel
SUMVISIBLE / COUNTVISIBLE / AVERAGEVISIBLE functions easily calculate the specified Range by ignoring any hidden cells, rows, or columns.
For those who cannot remember the formulae, download the Kutools from the web below,
Download Kutools from this File
1. Choose the cell you will place the summing result into, and in the "Kutools" menu, choose "Functions" then "Statistical &Math" " SUMVISIBLE (or AVERAGEVISBLE, COUNTVISIBLE as you wish).
2. Specify the ranges to which ignored hidden cells will be summed after opening the Function Arguments dialog box. Finish by clicking the OK button.
All hidden cells are ignored to enable the calculation of the summing result.
After a clear and well-elaborated guide, enjoy the easy maneuver of the excel workbook kudos.