[I Green]

OK I have a sheet that is a database where I have a col A is the date, col D
is the earnings.

What I want is to sum the earnings in col D subject to a range of dates.

sum(d4:D9999) where a4:a999=2009/6/1.and.a4:a999<=2009/6/30

So far I have experimented with conditionals and it keeps choking on me.

Once I have the formula, I wanted to cut and paste it into a different
sheet. Then I wanted to make a row for each month so I need something that is
easy to tweak for the rate range.

Thanks in advance for any advise.

[Marcelo]

Hello

If you are looking to sum all june or other month values you can use

=SUMPRODUCT(--(MONTH(A4:A999)=6),(D4:D999))

if you want to sum values between two date use

=SUMPRODUCT(--(A7:A124<=DATE(2009;6;30))*(A7:A124=DATE(2009;6;1 )),(D7:D124))

hth

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"I Green" escreveu:

OK I have a sheet that is a database where I have a col A is the date, col D
is the earnings.

What I want is to sum the earnings in col D subject to a range of dates.

sum(d4:D9999) where a4:a999=2009/6/1.and.a4:a999<=2009/6/30

So far I have experimented with conditionals and it keeps choking on me.

Once I have the formula, I wanted to cut and paste it into a different
sheet. Then I wanted to make a row for each month so I need something that is
easy to tweak for the rate range.

Thanks in advance for any advise.

[I Green]

I would like to also filter by year as the data source is a synoptic journal
that spans several years. Excel will hold a lot of data before it chokes.

The function you suggested works fine if the data source is this year only.

"Marcelo" wrote:

Hello

If you are looking to sum all june or other month values you can use

=SUMPRODUCT(--(MONTH(A4:A999)=6),(D4:D999))

if you want to sum values between two date use

=SUMPRODUCT(--(A7:A124<=DATE(2009;6;30))*(A7:A124=DATE(2009;6;1 )),(D7:D124))

hth

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"I Green" escreveu:

OK I have a sheet that is a database where I have a col A is the date, col D
is the earnings.

What I want is to sum the earnings in col D subject to a range of dates.

sum(d4:D9999) where a4:a999=2009/6/1.and.a4:a999<=2009/6/30

So far I have experimented with conditionals and it keeps choking on me.

Once I have the formula, I wanted to cut and paste it into a different
sheet. Then I wanted to make a row for each month so I need something that is
easy to tweak for the rate range.

Thanks in advance for any advise.

[T. Valko]

Just change the year number in the DATE function:

=SUMPRODUCT(--(A4:A999=DATE(2009,6,1)),--(A4:A999<=DATE(2009,6,30)),D7:D124)

Or, use cells to hold the date boundaries:

A1 = 6/1/2009
B1 = 6/30/2009

=SUMPRODUCT(--(A4:A999=A1),--(A4:A999<=B1),D7:D124)


--
Biff
Microsoft Excel MVP


"I Green" wrote in message
...
I would like to also filter by year as the data source is a synoptic
journal
that spans several years. Excel will hold a lot of data before it chokes.

The function you suggested works fine if the data source is this year
only.

"Marcelo" wrote:

Hello

If you are looking to sum all june or other month values you can use

=SUMPRODUCT(--(MONTH(A4:A999)=6),(D4:D999))

if you want to sum values between two date use

=SUMPRODUCT(--(A7:A124<=DATE(2009;6;30))*(A7:A124=DATE(2009;6;1 )),(D7:D124))

hth

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"I Green" escreveu:

OK I have a sheet that is a database where I have a col A is the date,
col D
is the earnings.

What I want is to sum the earnings in col D subject to a range of
dates.

sum(d4:D9999) where a4:a999=2009/6/1.and.a4:a999<=2009/6/30

So far I have experimented with conditionals and it keeps choking on
me.

Once I have the formula, I wanted to cut and paste it into a different
sheet. Then I wanted to make a row for each month so I need something
that is
easy to tweak for the rate range.

Thanks in advance for any advise.