Sum data between 2 dates ie 01Jun-30Jun

P

PamelaJ

I'm trying to sum information on another worksheet.

01-Jun in column a and 15 in column b
25-Jun in column a and 20 in column b
01-Aug in column a and 30 in column b

to give me total for Jun in another worksheet with a total value of 35
(ie adding just the month of June

Can anyone help?
 
B

Bondi

PamelaJ said:
I'm trying to sum information on another worksheet.

01-Jun in column a and 15 in column b
25-Jun in column a and 20 in column b
01-Aug in column a and 30 in column b

to give me total for Jun in another worksheet with a total value of 35
(ie adding just the month of June

Can anyone help?
Click to expand...

Hi Pamela,

Maybe you can use SUMPRODUCT() Something along the lines of:

=SUMPRODUCT(--(MONTH(A1:A3)=6),B1:B3)

Asuming here that the Dates are in A1:A3 and numbers in B1:B3.
Jou can just modify the range to your needs.
The 6 is for June and you can just alter that aswell to fit your needs.

Regards,
Bondi
 
D

David

An alternative would be to split the date so you have say: -

Row Column A Column B
1 2 June
2 24 June
3 13 April

You could then use SUMIF. Using the example above you would use the
following formula: -

=SUMIF(1B:3B,"June",1A:3A)

To explain the above: -

The formula first of all searches 1B:3B

The second part "June" is what it looks to find in the range 1B:3B

The final bit 1A:3A is the list of values and it calculates the sum of the
corresponding cells where it finds "June" or whatever criteria you set.

I hope that this helps.
 
P

PamelaJ

=SUMPRODUCT(--(MONTH('Month 4 July 06-07'!C$8:C$124)=6),'Month 4 July
06-07'!D$8:D$124)

This Gives me monthly summary totals
June, July .....May for the 12 months.

This is where I was finally going
Column A
=IF(MONTH('Month 4 July 06-07'!$C8)=B$2,'Month 4 July 06-07'!$A8,0)
Column B
=IF(MONTH('Month 4 July 06-07'!$C8)=+B$2,'Month 4 July 06-07'!$D8,0)


C8 is date in formate 01/06/06 etc
A8 is Code Number
D8 is Value

This gave me the Product Code and the quantity completed in month
detail.

I then copied down to C9, A9, D9 etc then sum total at bottom, month by
month for 12 months in columns C,D etc.. etc..

Thanks Bondi, I will definitely be back to this site, sat up until 3am
this morning trying to figure this one out.
 
P

PamelaJ

Thanks for the post David, it's a much simpler version, bu
unfortunately I'm using another persons file, exported from a syste
and there is too much data to amend. However, will utilize that whe
creating my own files.
Thanks again for response.
Pamela
 
Top