DSUM
 

I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so would
greatly
appreciate some help.

DSUM
 

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so would
greatly
appreciate some help.

DSUM
 

In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so would
greatly
appreciate some help.

DSUM
 

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so
would
greatly
appreciate some help.

DSUM
 

Thanks T.V. I have no idea what was wrong but I copied your formula in again
and it works perfect. A special thanks for the extra effort of producing the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so
would
greatly
appreciate some help.

DSUM
 

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so
would
greatly
appreciate some help.

DSUM
 

Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89 0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to accurately
work.

There is only one actual value in this range of $1667.82 yet I am getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success so
would
greatly
appreciate some help.

DSUM
 

Replace the * with a comma followed by a double unary --

........{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to accurately
work.

There is only one actual value in this range of $1667.82 yet I am getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells
I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no success
so
would
greatly
appreciate some help.

DSUM
 

Actually, it'll work either way. I just like to keep things consistent.

Here's an updated sample file:

sesler2.1.xls 14kb

http://cjoint.com/?cChd7gCXvo

In the sample file your column F is represented in column G.

Biff

"T. Valko" wrote in message
...
Replace the * with a comma followed by a double unary --

.......{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to accurately
work.

There is only one actual value in this range of $1667.82 yet I am getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2
cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no
success so
would
greatly
appreciate some help.

DSUM
 

I still have problems but don't know how to attach a file in a post? so I can
show you what I am doing

"T. Valko" wrote:

Actually, it'll work either way. I just like to keep things consistent.

Here's an updated sample file:

sesler2.1.xls 14kb

http://cjoint.com/?cChd7gCXvo

In the sample file your column F is represented in column G.

Biff

"T. Valko" wrote in message
...
Replace the * with a comma followed by a double unary --

.......{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to accurately
work.

There is only one actual value in this range of $1667.82 yet I am getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2
cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no
success so
would
greatly
appreciate some help.

DSUM
 

Send a copy of the file to me. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Include an explanation!

Biff

"sesler2" wrote in message
...
I still have problems but don't know how to attach a file in a post? so I
can
show you what I am doing

"T. Valko" wrote:

Actually, it'll work either way. I just like to keep things consistent.

Here's an updated sample file:

sesler2.1.xls 14kb

http://cjoint.com/?cChd7gCXvo

In the sample file your column F is represented in column G.

Biff

"T. Valko" wrote in message
...
Replace the * with a comma followed by a double unary --

.......{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw
dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to
accurately
work.

There is only one actual value in this range of $1667.82 yet I am
getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula
in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2
cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 =
14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no
success so
would
greatly
appreciate some help.

DSUM
 

It still isn't working I'm afraid. The value returned is incorrect as I have
manually checked it. Could you please advise me how to attach a file with a
post so I can send the file in question.

"T. Valko" wrote:

Actually, it'll work either way. I just like to keep things consistent.

Here's an updated sample file:

sesler2.1.xls 14kb

http://cjoint.com/?cChd7gCXvo

In the sample file your column F is represented in column G.

Biff

"T. Valko" wrote in message
...
Replace the * with a comma followed by a double unary --

.......{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to accurately
work.

There is only one actual value in this range of $1667.82 yet I am getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2
cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no
success so
would
greatly
appreciate some help.

DSUM
 

Send the file to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"sesler2" wrote in message
...
It still isn't working I'm afraid. The value returned is incorrect as I
have
manually checked it. Could you please advise me how to attach a file with
a
post so I can send the file in question.

"T. Valko" wrote:

Actually, it'll work either way. I just like to keep things consistent.

Here's an updated sample file:

sesler2.1.xls 14kb

http://cjoint.com/?cChd7gCXvo

In the sample file your column F is represented in column G.

Biff

"T. Valko" wrote in message
...
Replace the * with a comma followed by a double unary --

.......{1;1;1})0),--($F$2:$F$89.........

Biff

"sesler2" wrote in message
...
Hi Biff
I'm using your calculation you gave me but was wanting to add one more
condition:
The values in column F have to be 0
This is what I have tried:
=SUMPRODUCT(--(MMULT(($J$2:$L$89=$A$101)*($J$2:$L$89<=$A$102),{ 1;1;1})0)*($F$2:$F$89
0),$G$2:$G$89)

So, to recap, columns J, K, and L are Accept, Decline and Withdraw
dates
respectively
A101 = 01/01/07
A102 = 31/01/07
column F is actual $ values
coumn G is estimate $ values.

I don't want to sum estimated values unless they have an actual value

I'm sure it's just my parentheses placement but cant get it to
accurately
work.

There is only one actual value in this range of $1667.82 yet I am
getting
calculated value of $6345.55

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"sesler2" wrote in message
...
Thanks T.V. I have no idea what was wrong but I copied your formula
in
again
and it works perfect. A special thanks for the extra effort of
producing
the
file it was very much appreciated. Your'e a champ!

"T. Valko" wrote:

Should work!

Are you sure *all* the dates are true Excel dates?

Are the values in $Value numeric numbers and not TEXT numbers?

Here's a small sample file that demonstrates this:

sesler2.xls 14kb

http://cjoint.com/?cwftMd37rp

The dates highlighted in yellow fall within your date range. The
values
highlighted in green are those that are summed.

Biff

"sesler2" wrote in message
...
In my spreadsheet
D2:D58 = Accepted Date
E2:E58 = Withdrawn Date
F2:F58 = Declined Date
C2:C58 = $Value
A67 = 01/02/2007
B67 = 14/02/2007
and this is the formula I typed in :
=SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58)
Yet it returns 0.
I must be doing something wrong here?

"T. Valko" wrote:

Try this:

Assume:

A2:A20 = Accepted Date
B2:B20 = Withdrawn Date
C2:C20 = Declined Date
D2:D20 = $Value
A67 = 01/02/2007
B67 = 14/02/2007

=SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20)

Biff

"sesler2" wrote in message
...
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2
cells I
have
start and end dates. i.e. a67 = 01/02/2007 and b67 =
14/02/2007.

I would like to sum the $Value column if:
(Accepted Date is = a67 and <= b67) or
(Withdrawn Date is = a67 and <= b67) or
(Declined Date is = a67 and <= b67). I have tried to no
success so
would
greatly
appreciate some help.