Compute subtotal rows in a spreadsheet

Question!

I have a spreadsheet that looks like this:

   A         B               C
1  Section   Task            Dev Hours
2  4         PROJECT TOTAL   =c3+....
3  4.1       Public site     =c4+c5+...
4  4.1.1     User Accounts   2
5  4.1.2     Daily Deal      =sum(c6:c7)
6  4.1.2.1   Model           4
7  4.1.2.2   View            2

What I'd like is a better way to compute subtotals in column C for non-leaf rows. Using =sum is error-prone as I add/remove rows. Also I can only use it when all children are leaves, or it will double-count things. Using addition is even more error-prone and extra annoying. Is there some way to tell {Excel,OpenOffice,LibreOffice,Google Documents} to roll up the values in column C for all following child rows, based on the outline numbers in column A? I would love a function like this:

=sum_children(A2)

Where A2 is the cell with the current row's outline number.



Answers

You can use an array formula to do this. For the formula below, I've assumed that your Section values are sorted as you have shown above. In C2, paste the following and enter the formula by pressing Ctrl Shift Enter:

=SUM(IF(LEFT($A3:$A$7,LEN(A2))=A2,IF(ISERROR(FIND(".",$A3:$A$7,LEN(A2) 2)),$C3:$C$7,0),0))

You should then be able to copy cell C2 and paste onto any of the cells below that represent a parent section.

Basically this formula checks Column A below the current row for strings that start with the current section and only sums the dev hours for those that don't have an additional period after the current section "."

To avoid having to update these formulas when you add additional sections to your table, you can make the endpoints of the arrays (A7 and C7 in the example formula) a row way beyond the bottom of your table (e.g. A50 and C50). This could affect performance slightly, but really only if you go overboard and give yourself a 50,000 row pad.

By : Excellll


This video can help you solving your question :)
By: admin