Complex Conditional Sum
 

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.

Complex Conditional Sum
 

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.

Complex Conditional Sum
 

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.

Complex Conditional Sum
 

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.