moving Subtotals in Excel 1

[Kathrynlewis]

I need to move subtotals to a different cell, and want to
do it in a macro. Currently the subtotals are appearing below the last value of a PO Group. I need them to appear to the right (in the totals column,as shown above) of the last poNumber group.I want it to look like the following:
columnA columnB ColumnC
Po number value total
R1234 5.00
R1234 2.00
R1234 1.00 8.00

S9876 3.00
S9876 .50 3.50

T5432 .75 .75

 

[Hasit]

Not sure I see the problem here, but rather than use a macro, can you not set up a formula in column C with the appropriate formula (such as SUBTOTAL) and subtotal your values that way?

 

[Kathrynlewis]

Hasit: When I subtotal the values, they show up beneath
the groupings in column B. i need them to show up in column C, on the last line of the grouping. Can you suggest a formula to write to correct this? i've never done this before. Thanks

 

[xlhelp]

Hi, Kathryn

Your example range would be in cells A1:B8

If you type the formula
=SUMIF($A$1:$A$8,A3,$B$1:$B$8)
in cell C3 and then copy it down to C6 and C8, you will get what you are looking for.

HTH

Indu

 

[GlennUK]

Hi Kathyrnlewis,

the formula you need ( in C1 ) is ...

=IF(A1<>A2,SUM(OFFSET(A1,-COUNTIF(A:A,&quot;=&quot;&A1)+1,1,COUNTIF(A:A,&quot;=&quot;&A1),1)),&quot;&quot

and then you can copy this as far down column C as you need. It will only show a result in the places you want.

Cheers, Glenn.

 

[Hasit]

Glenn: Brilliant! Worthy of a star.

 

[xlhelp]

Hi, GlennK

Recommend a slight change in your formula since Kathryn has a blank row occuring between data sets

=IF(A1<>A2,IF(A1=&quot;&quot;,&quot;&quot;,SUM(OFFSET(A1,-COUNTIF(A:A,&quot;=&quot;&A1)+1,1,COUNTIF(A:A,&quot;=&quot;&A1),1))),&quot;&quot

Thanks.

Indu

 

[Kathrynlewis]

GlennUK,XLHElp,and Hasit:
I just wanted to thank everyone for walking me through this. The last formula worked best for my needs but thank you so much for all the suggestions, will keep them on file for next time.

 

[Kathrynlewis]

The values are not transferring properly. For instance, when the formula is entered, it appears as though all is correct, however, when I do a grand total sum, the grand total is wrong. One case in point:I had 12 rows each valued at $12.00- the calculation was in he proper cell, however the calculation was $444.00, instead of $144.00 Thanks for any help you can offer.

 

[xlhelp]

Hi, Kathryn!

Is it possible I could see the file?

You could send it to xlwrdhlp@clarica.com

Thanks.

Indu