Question Grouping rows in Table

Dan Bliss

New member
Joined
Dec 7, 2016
Messages
45
Reaction score
0
Points
0
Excel Version(s)
Office 365
Outlining or grouping contiguous rows works as shown using Data>>Group after highlighting rows. Question: Does grouping work on a Table? If so, how so that I can get a result similar to what shows in image on right below.
I find in a table, the =SUM[Pop in M] sets to 0 when rows are grouped.
ExcelGroup_pic1.JPGExcelGroup_pic2.JPG
 
Thanks, NBVC for your input. Ah! If only I had been a bit more observant... I used AutoSum on the Home ribbon when creating a sum of a column of a table. It defaults using SUBTOTAL function. SUBTOTAL function's first parameter is function_num, which when > 100, as in images 1 and 2 below (function_num is 109) ignore hidden row values. If I edit the value of function_num to 9 so that expression is: =SUBTOTAL(9,[Pop in M]), then hidden values are not ignored and the result in B5 is 70, left undisturbed when the group is collapsed/hidden.
SUBTOTAL nicely documented at: https://support.office.com/en-us/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939


ExcelGroup_pic3.JPGExcelGroup_pic4.JPGExcelGroup_pic7.JPG
 

Attachments

  • ExcelGroup_pic5.JPG
    ExcelGroup_pic5.JPG
    37.6 KB · Views: 9
  • ExcelGroup_pic6.JPG
    ExcelGroup_pic6.JPG
    36.4 KB · Views: 10
Back
Top