Subtotal at the top instead of the bottom?


T

Tacrier

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
 
Ad

Advertisements

T

Tacrier

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. :)
 
S

Sheeloo

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
 
D

Dave Peterson

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).
 
Ad

Advertisements

P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top