If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subtotal at the top instead of the bottom?
Is there a way to subtotal B:C columns into column D instead of at an
inserted row at the bottom of change in Column A? I would like to have the subtotal show in column at each change in column A. A B C D Name Basic Costs Other Costs Total Costs Person 1 4,154.00 4,477.27 Person 1 305.05 Person 1 18.22 Person 2 572.10 806.60 Person 2 234.50 Person 3 701.40 701.40 Person 4 300.00 2,646.49 Person 4 639.60 Person 4 57.10 Person 4 350.00 Person 4 991.38 Person 4 80.60 Person 4 227.81 Person 5 991.38 1,181.98 Person 5 80.60 Person 5 110.00 I am stuck...again. :s |
#2
|
|||
|
|||
Subtotal at the top instead of the bottom?
Sorry, I missed a couple words in my question, please disregard previous post
and have a look at this one: Is there a way to subtotal B:C columns into column D instead of at an inserted row at the bottom of change in Column A? I would like to have the subtotal show in column D at each change in column A. For example, Person 1's basic and other costs total 4477.27, shown in the first row containing "Person 1" A B C D Name Basic Costs Other Costs Total Costs Person 1 4,154.00 4,477.27 Person 1 305.05 Person 1 18.22 Person 2 572.10 806.60 Person 2 234.50 Person 3 701.40 701.40 Person 4 300.00 2,646.49 Person 4 639.60 Person 4 57.10 Person 4 350.00 Person 4 991.38 Person 4 80.60 Person 4 227.81 Person 5 991.38 1,181.98 Person 5 80.60 Person 5 110.00 Any suggestions are valuable and appreciated. |
#3
|
|||
|
|||
Subtotal at the top instead of the bottom?
1. Enter this Formula in cell E2
=SUMPRODUCT(--($A$2:$A$1000=A2))-SUMPRODUCT(--(A2:$A$1000=A2))+1 and copy down after changing 1000 to the last row number in your data set 2. Enter this in D2 =SUMPRODUCT(--($A$1:$A$1000=A2),$B$1:$B$1000) 3. Then filter on Col E for values equal to 1 and paste the formula in D2 to the filtered cells in Col D "Tacrier" wrote: Is there a way to subtotal B:C columns into column D instead of at an inserted row at the bottom of change in Column A? I would like to have the subtotal show in column at each change in column A. A B C D Name Basic Costs Other Costs Total Costs Person 1 4,154.00 4,477.27 Person 1 305.05 Person 1 18.22 Person 2 572.10 806.60 Person 2 234.50 Person 3 701.40 701.40 Person 4 300.00 2,646.49 Person 4 639.60 Person 4 57.10 Person 4 350.00 Person 4 991.38 Person 4 80.60 Person 4 227.81 Person 5 991.38 1,181.98 Person 5 80.60 Person 5 110.00 I am stuck...again. :s |
#4
|
|||
|
|||
Subtotal at the top instead of the bottom?
How about just using a formula in the data (before using data|subtotal) that
adds column B plus C. =sum(b2:c2) and drag down Then do the subtotals and only sum column D (at each change in column A). Tacrier wrote: Sorry, I missed a couple words in my question, please disregard previous post and have a look at this one: Is there a way to subtotal B:C columns into column D instead of at an inserted row at the bottom of change in Column A? I would like to have the subtotal show in column D at each change in column A. For example, Person 1's basic and other costs total 4477.27, shown in the first row containing "Person 1" A B C D Name Basic Costs Other Costs Total Costs Person 1 4,154.00 4,477.27 Person 1 305.05 Person 1 18.22 Person 2 572.10 806.60 Person 2 234.50 Person 3 701.40 701.40 Person 4 300.00 2,646.49 Person 4 639.60 Person 4 57.10 Person 4 350.00 Person 4 991.38 Person 4 80.60 Person 4 227.81 Person 5 991.38 1,181.98 Person 5 80.60 Person 5 110.00 Any suggestions are valuable and appreciated. -- Dave Peterson |
#5
|
|||
|
|||
Subtotal at the top instead of the bottom?
Depending on how many names you've got, you might like to produce a summary
table as follows: in column F (say), put the names like this: F1: Person 1 F2: Person 2 F3: Person 3 F4: Person 4 etc. Then in G1 you can have this formula: =SUMIF(A:A,F1,B:B)+SUMIF(A:A,F1,C:C) then copy it down for as many names as you have. Hope this helps. Pete "Tacrier" . wrote in message ... Sorry, I missed a couple words in my question, please disregard previous post and have a look at this one: Is there a way to subtotal B:C columns into column D instead of at an inserted row at the bottom of change in Column A? I would like to have the subtotal show in column D at each change in column A. For example, Person 1's basic and other costs total 4477.27, shown in the first row containing "Person 1" A B C D Name Basic Costs Other Costs Total Costs Person 1 4,154.00 4,477.27 Person 1 305.05 Person 1 18.22 Person 2 572.10 806.60 Person 2 234.50 Person 3 701.40 701.40 Person 4 300.00 2,646.49 Person 4 639.60 Person 4 57.10 Person 4 350.00 Person 4 991.38 Person 4 80.60 Person 4 227.81 Person 5 991.38 1,181.98 Person 5 80.60 Person 5 110.00 Any suggestions are valuable and appreciated. |
Thread Tools | |
Display Modes | |
|
|