sum( ) columns with labels

%3CLINGO-SUB%20id%3D%22lingo-sub-3416963%22%20slang%3D%22en-US%22%3Esum(%20)%20columns%20with%20labels%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3416963%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20attendance%20sheet%20with%20dates%20across%20the%20column%20tops%20and%20members%20down%20the%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20groups%20of%20members%20that%20need%20to%20be%20summed%20as%20a%20group%20and%20added%20to%20the%20grand%20total.%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20use%20labels%20so%20that%20when%20members%20are%20added%20or%20removed%20and%20the%20group%20sums%20move%2C%20the%20labels%20allow%20the%20grand%20total%20to%20be%20correct.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20a%20%221%22%20the%20member%20is%20present.%3C%2FP%3E%3CP%3EI%20have%20it%20working%20for%20one%20column%20(below).%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20extend%20the%20label%20to%20all%20columns%20(GroupA%3D%24B%246%3A%24b%24J)%2C%20it%20sums%20up%20all%20columns%20and%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20it%20sum%20all%20group%20columns%20individually%20and%20sum%20all%20group%20sums%20(by%20label)%20to%20a%20grand%20total%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it%20would%20work%20if%20I%20gave%20each%20column%20sum%20a%20name%2C%20but%20I'd%20rather%20have%20the%20column%20sum%20one%20name%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MCBinder_0-1653289115202.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374112i6D1AC4875183F866%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MCBinder_0-1653289115202.png%22%20alt%3D%22MCBinder_0-1653289115202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MCBinder_2-1653289213180.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374114i5DC28DE9FEF400B3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MCBinder_2-1653289213180.png%22%20alt%3D%22MCBinder_2-1653289213180.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3416963%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3417104%22%20slang%3D%22en-US%22%3ERe%3A%20sum(%20)%20columns%20with%20labels%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3417104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398056%22%20target%3D%22_blank%22%3E%40MCBinder%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20define%20the%20groups%20in%20the%20sheet%20itself%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1455.png%22%20style%3D%22width%3A%20731px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374280i82D667D6ECE31BFE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1455.png%22%20alt%3D%22S1455.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20anywhere%20in%20the%20data%2C%20then%20click%20Subtotal%20on%20the%20Data%20tab%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3ESpecify%20that%20you%20want%20a%20subtotal%20on%20each%20change%20in%20Group%2C%20and%20that%20you%20want%20subtotals%20for%20each%20of%20the%20columns%20labeled%201%2C%202%2C%20...%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1456.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374282i2A88ACD9B8AB4C5F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1456.png%22%20alt%3D%22S1456.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20OK.%20You%20get%20group%20subtotals%20and%20grand%20totals%2C%20and%20outline%20buttons%20to%20expand%2Fcollapse%20the%20data.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1457.png%22%20style%3D%22width%3A%20822px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374284i26007D11D00DE77C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1457.png%22%20alt%3D%22S1457.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have an attendance sheet with dates across the column tops and members down the rows. 

There are groups of members that need to be summed as a group and added to the grand total. 

I'd like to use labels so that when members are added or removed and the group sums move, the labels allow the grand total to be correct. 

 

Enter a "1" the member is present.

I have it working for one column (below). 

If I extend the label to all columns (GroupA=$B$6:$b$J), it sums up all columns and rows.

 

How can I make it sum all group columns individually and sum all group sums (by label) to a grand total? 

 

I think it would work if I gave each column sum a name, but I'd rather have the column sum one name, 

 

 

MCBinder_0-1653289115202.png

MCBinder_2-1653289213180.png

 

1 Reply

@MCBinder 

I'd define the groups in the sheet itself:

S1455.png

Click anywhere in the data, then click Subtotal on the Data tab of the ribbon.

Specify that you want a subtotal on each change in Group, and that you want subtotals for each of the columns labeled 1, 2, ...:

S1456.png

Click OK. You get group subtotals and grand totals, and outline buttons to expand/collapse the data.

S1457.png