DSUM

[Guest]

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. Valko]

Try this:

Assume:

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

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

Biff

 

[Guest]

In my spreadsheet
D258 = 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]

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

 

[Guest]

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]

You're welcome. Thanks for the feedback!

Biff

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!

 

[Guest]

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]

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

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

Biff

 

[T. Valko]

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

 

[Guest]

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]

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

 

[Guest]

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]

Send the file to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff