subtotal by a range of dates

[rosa]

OK: I know there must be an esier way to do this, i'm working with a data
base in wich i have a column of dates and next to it a column with quantities
i need to subtotal the quantities in a monthly basis example: all may dates
and total by quantity.

5/19/2006 10400 275,000
5/30/2006 18000 189,906
6/2/2006 3200 275,000
6/2/2006 22000 189,906
6/12/2006 22000 189,906
6/16/2006 12800 275,000
want to separete may from june and sum quantities from each month. anybody???

let me know if you need more info.

thx

 

[Bob Greenblatt]

5/19/2006 10400 275,000
5/30/2006 18000 189,906
6/2/2006 3200 275,000
6/2/2006 22000 189,906
6/12/2006 22000 189,906
6/16/2006 12800 275,000

I suggest you add another column containing the month. Then use Subtotal
from the data menu.

 

[Domenic]

Assuming that A2:C7 contains the data, let E2 and E3 contain 5/1/2006
and 6/1/2006, then enter the following formula in F2 and copy down:

=SUMPRODUCT(--($A$2:$A$7-DAY($A$2:$A$7)+1=E2),$B$2:$B$7)

Hope this helps!

 

[rosa]

Dominic:
i think what you're saying it's what i'm looking for, is there a way i can
send you my database by e mail and maybe you can see the whole database????
if you are up to it? or i can call you???? let me know

 

[Domenic]

Have you tried my solution?

 

[rosa]

yes, but i'm not so good with formulas, i don't know what i'm doing wrong, my
database has other columns and information. it's not coming out right i have
a lot of diff dates.

 

[Bernard Rey]

rosa :

OK: I know there must be an esier way to do this, i'm working with a data
base in wich i have a column of dates and next to it a column with quantities
i need to subtotal the quantities in a monthly basis example: all may dates
and total by quantity.

5/19/2006 10400 275,000
5/30/2006 18000 189,906
6/2/2006 3200 275,000
6/2/2006 22000 189,906
6/12/2006 22000 189,906
6/16/2006 12800 275,000
want to separete may from june and sum quantities from each month. anybody???

Another formula could be:

=SUM(IF(MONTH($A$2:$A$100)=5,$B$2:$B$100,0))

....in which the dates are in cells A2 down to A100 and the quantities in
celles B2 down to B100 (you'll have ot adjust it to your sheet).

This formula has to be validated holding down the "Apple" Key while pressing
the "Enter" key. When you do that, you'll notice the formula displays with
brackets:

{=SUM(IF(MONTH($A$2:$A$100)=5,$B$2:$B$100,0))}

Of course, you can then easily have the quantities for the other months
changing the formula to:

=SUM(IF(MONTH($A$2:$A$100)=6,$B$2:$B$100,0))

....for June, and so on. Notice you'll have to validate holding the "Apple"
Key each and everytime you'll edit the cell (and if you don't, the brackets
are gone, and the result too).

 

[Skin]

My method is in another column on the same row enter the formula =Month(A1)
that is if the date is in A1, if date is in B1 change formula to =Month(B1)
Then fill down. Now you can filter and subtotal by month

 

[JE McGimpsey]

OK: I know there must be an esier way to do this, i'm working with a data
base in wich i have a column of dates and next to it a column with quantities
i need to subtotal the quantities in a monthly basis example: all may dates
and total by quantity.

5/19/2006 10400 275,000
5/30/2006 18000 189,906
6/2/2006 3200 275,000
6/2/2006 22000 189,906
6/12/2006 22000 189,906
6/16/2006 12800 275,000
want to separete may from june and sum quantities from each month. anybody???

The easiest way to do this, IMO, is to use a Pivot Table. Use the date
in the row field. In your PT, ctrl-click the date field header, choose
Group, and group by months.

 

[rosa]

thank you all, i will take you for dinner.. but i don't know where you are
you've been a great help, have to turn my work today i'll try everything !!!!!

 

[Domenic]

yes, but i'm not so good with formulas, i don't know what i'm doing wrong, my
database has other columns and information. it's not coming out right i have
a lot of diff dates.

What exactly is happening? Are you getting an incorrect result?

 

[SteveF]

The easiest solution I know is to insert a column and use an EOMONTH
function, which will return the last day of the month for each record. You
may have to activate it using the Data Analysis Add-in (a 10 second process).

Assuming your dates are in column A, your formula would be: =EOMONTH(A1,0)
and it would return the date code for May 31, 2006. This gets you around the
issue of years if your data goes back more than 12 months and you can easily
summarize it using a pivot table or the Subtotals function in the Data menu.

 

[JE McGimpsey]

The easiest solution I know is to insert a column and use an EOMONTH
function, which will return the last day of the month for each record. You
may have to activate it using the Data Analysis Add-in (a 10 second process).

Not all companies or institutions allow installation of the Analysis
Toolpak Add-in. For those that don't (or if you just don't want to use
that add-in), an equivalent to

=EOMONTH(A1, n)

is

=DATE(YEAR(A1), MONTH(A1) + n + 1, 0)

This works since, to XL, the "zeroth" day of any month is the last day
of the previous month.