# 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

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

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.