Subtotals for each month

[Lino]

I am trying to get subtotals by month programatically.

Is there a function that exists that can do this otherwise any suggestions on doing this programatically?

My dates are in the format 20020502 (this being today's date).

The dates are in column A and I want it to add the values in column D (for each month) and write them to another cell.

Example:

column A Column D

20020427 1000
20020428 1000
20020429 1000
20020430 1000
20020501 1000
20020502 1000

Programatically it would declare the results as:

Column E Column F
April 4000
May 2000

Any suggestions?

[Aladin Akyurek]

On 2002-05-02 08:38, Lino wrote:
I am trying to get subtotals by month programatically.

Is there a function that exists that can do this otherwise any suggestions on doing this programatically?

My dates are in the format 20020502 (this being today's date).

The dates are in column A and I want it to add the values in column D (for each month) and write them to another cell.

Example:

column A Column D

20020427 1000
20020428 1000
20020429 1000
20020430 1000
20020501 1000
20020502 1000

Programatically it would declare the results as:

Column E Column F
April 4000
May 2000

Any suggestions?

Just because you chose not to represent dates as usual, you need an aggressive formula to get the desired results.

Assume the sample data to be in A2:B7.

Create a list of month numbers in D from D2 on.
Create a list of 3-letter month names in E from E2 on. all this should look like:

{1,"Jan";
2,"Feb";
3,"Mar";
4,"Apr";
5,"May";
6,"Jun";
7,"Jul";
8,"Aug";
9,"Sep";
10,"Oct";
11,"Nov";
12,"Dec"}

In F2 enter the following formula and copy down:

=SUMPRODUCT((MID($A$2:$A$7,5,2)+0=E2)*($B$2:$B$7))

Aladin

Postscript:

I see I didn't pay attention to your data columns, but I trust you can adjust to suit the foregoing .

[ This Message was edited by: Aladin Akyurek on 2002-05-02 08:57 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-02 08:58 ]