DSUM

S

sesler2

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.
 
T

T. Valko

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
 
S

sesler2

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
Click to expand...
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

T. Valko

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
 
S

sesler2

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

T. Valko

You're welcome. Thanks for the feedback!

Biff

sesler2 said:
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!
Click to expand...
 
S

sesler2

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

T. Valko

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

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

Biff
 
S

sesler2

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

T. Valko

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
 
S

sesler2

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

T. Valko

Send the file to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff
 
Top