To find subtotal - Please help

[pol]

Hi all, I have an excel with the following format. I have to find out
subtotal of the each date column. The empty date column should be considered
under the group of previous column having date. Please help

Date Amount
A1 B1 C1
01.01.2010 2000 Cumi.Sum
-200
-300
-1500 0.00

10.01.2010 500
-300
-200 0.00

20.01.2010 700
-200 500.00

30.01.2010 400
-400 0.00



With thanks and regards

Pol

 

[Jacob Skaria]

If you have valid excel dates in ColA the below will work. In cell C1 enter
the below and copy down as required.

=IF(B1="",SUM(OFFSET(A1,0,1,-(ROW()-MATCH(9^9,$A$1:A1)+1))),"")

 

[trip_to_tokyo]

Hi Pol I have just put up a file for you at:-

http://www.pierrefondes.com/

It is item number 56 towards the top of my home page.

This file is prepared in EXCEL 2007.

1. I have copied your data into Sheet2.

2. In Sheet2 you will notice that I have changed column A so that dates
appear in all of the rows.

3. Now take the following action in Sheet2:-

- highlight A1:B12

- Data / Outline group / Subtotal /

Subtotal pane should launch.

4. The Subtotal pop up window should now appear as follows:-

(i) At each change in:

Date

(ii) Use function:

Sum

(iii) Add subtotal to:

Amount

The above field should have a tick in the box on the left.

5. Click OK in the above window.

Adjust column widths.

You should now get the subtotals that you require.

If my comments have helped please hit Yes.

Thanks.

 

[pol]

Many thanks for the reply.

I cannot apply that formula , I am looking for a macro to subtotal
automatically the cumilative column of the last row of the group data. Also
the date column cannot considered as date column format. But it would be a
text column and group.

Please help

 

[מיכ×ל (מיקי) ×בידן]

Pol,
Take a good look here
http://www.microsoft.com/office/com...1c70f5-2fd1-4683-b3fb-6a254edfa152&sloc=en-us
Micky

 

[מיכ×ל (מיקי) ×בידן]

I assume you meant something like that:
=IF(AND(A1="",A2<>""),SUM(OFFSET(A1,0,1,-(ROW()-MATCH(9^9,$A$1:A1)+1))),"")
Micky

 

[Jacob Skaria]

Try this macro

Sub MyMacro()
Dim lngRow As Long, varSum As Variant
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row + 1
If Range("B" & lngRow) <> "" Then
varSum = varSum + Range("B" & lngRow)
Else
Range("C" & lngRow) = varSum: varSum = 0
End If
Next
End Sub

 

[Jacob Skaria]

Hi Micky

I thought there is a blank row between each sections and hence the +1 in the
formula.. Even so that should have been AND(A1="",A2<>""), to avoid
calculations after the data....Thanks for pointing that out...

 

[pol]

Thanks for the reply Jacob But still have some problem. There is no any blank
line between each group. I have to write the cumilative sum just before
starting the net row. The next group will start Just after the previous group
data ends. I thnk the follwing will macors will work

lastrow = Range("B" & Rows.Count).End(xlUp).Row
currow = ActiveCell.Row
Col_A = ""

For RowCount = 1 To lastrow
If RowCount >= currow Then
If Range("A" & RowCount) = "" Or Range("A" & RowCount) = 0 Then
Range("A" & RowCount) = Col_A
Else
Col_A = Range("A" & RowCount)
End If
End If

Next RowCount
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(24), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Please advice me

With thanks and regards
Pol