Displaying a cumulative total
You can use the COUNT and SUM functions to perform a cumulative sum on a range of cells. This formula ignores empty cells or those cells that contain text. If a number is later added to an empty cell, the formula is automatically updated to include the new data. This is a useful function to know when you are working with the Target Value in a Gauge component.
Example:
Goal: Use the COUNT and SUM functions to display a cumulative total.
SUM( SLICE( A2:L2 , 0 , COUNT( A2:L2 ) ) , data ) ...
Location | Action | For our example |
From Select data or start formula | Highlight all the cells in the selected range, including empty cells or cells that contain text | A2:L2 |
click Wrap current value in function, Data Manipulation, SLICE | SLICE( A2:L2 , start, end ) ... | |
With start selected | type 0, click Apply | |
With end selected | click Insert function, Maths, COUNT | SLICE( A2:L2 , 0 , COUNT( data ) ) ... |
With data selected | Highlight all the cells in the selected range, including empty cells or cells that contain text | SLICE( A2:L2 , 0 , COUNT( A2:L2 ) ) ... |
Click SLICE to highlight the whole function, click Wrap current value in function, SUM | SUM( SLICE( A2:L2 , 0 , COUNT( A2:L2 ) ) , data ) ... | |
Click SUM to highlight the whole function, click Evaluate current selection | Our result is 100. |