Discussion
In MS Excel, what is the difference between SUM and SUBTOTAL when rows are hidden by a filter?
Answer: A
Correct answer: SUBTOTAL can ignore filtered-out rows while SUM always includes them
SUM adds every cell in its range, whether or not the rows are visible. SUBTOTAL ignores rows hidden by a filter, so a filtered total reflects only what is displayed. That is why the Total Row of an Excel Table uses SUBTOTAL, and why the Subtotal command on the Data tab inserts it rather than SUM. Its first argument selects the operation and, importantly, the behaviour towards manually hidden rows. Codes 1 to 11 - where 9 is SUM and 1 is AVERAGE - ignore filtered rows but include rows hidden by hand. Codes 101 to 111 ignore both. So =SUBTOTAL(9,B2:B100) and =SUBTOTAL(109,B2:B100) differ only when rows have been hidden manually. SUBTOTAL also skips any nested SUBTOTAL within its range, which is what allows subtotals inside a grand total without double counting. AGGREGATE extends the idea further, adding options to ignore error values as well.
Post your comment
Please log in or register to post a comment.
Examination Categories
- SSC Exams
- Banking Exams
- Teaching Exams
- Civil Services Exams
- Railways Exams
- Engineering Recruitment Exams
- Defence Exams
- State Government Exams
- Police Exams
- Insurance Exams
- Nursing Exams
- Other Government Exams
- NRA CET
- PG Entrance Exams
- Campus Placement
- MBA Entrance Exams
- CUET and UG Entrance
- Judiciary Exams
- Regulatory Body Exams
- Software Development