Certainly use the summation formulas when working with Excel. Right here with to summing columns is perhaps by far valigaste work step in everyday Excel Work; an operation which normally is perceived as simple and safe.
But the article below shows some considerable risks that can occur when you add up Filtered columns.
|
SUBTOTAL / SUB – Summary of filtered lists in Excel
|
| Summarizes always right given Excelfilens future use?
The risk is great that you (or colleague) in the future will turn on the filter function in a certain Excelbok. Then you can no longer assume that the summations in Excel match anymore. A column that has a sum formula at the bottom, and then filtered will still show the total for all lines, whether they appear or not (that has been filtered).
The trick is in understanding the difference between the following two formulas:
- SUM (SUMMA) – the usual formula for summing
- SUBTOTAL (SUBTOTAL) – the more “filtervänliga” Formula for summing
Simply put, you must use SUBTOTAL (SUBTOTAL) when sums the columns filtered. In all other cases, the usual SUM (SUMMA) excellent. In a non-filtered list gives formulas same outcome.
I describe the problem (and the solution) with a simple video examples on the following page on ExcelKungen.com:
SUBTOTAL / SUB – Summary of filtered lists in Excel
Today BUTTON COMBINATION
SHIFT + SPACE – select an entire row (the current row that you are).
Read more about keyboard shortcuts in Excel:
http://www.excelkungen.com/exceltrix/shortcuts-genvagar-i-excel/ |
|
|
