If you want to sum the Payment column E where A is between certain dates and
where column H = A6 then you can use

=SUMPRODUCT(--(Payments!$A$2:$A$13=DATE(2004,6,4)),--(Payments!$A$2:$A$13<=DATE(2005,5,4)),--(Payments!$H$2:$H$13=$A6),Payments!$E$2:$E$13)

no need to array enter

if want to use a dynamic range which can be found here

http://www.contextures.com/xlNames01.html#Dynamic

--
Regards,

Peo Sjoblom

Portland, Oregon




"willee" wrote in
message ...

Hello folks,

Been scratching my head for a couple of hours trying to avoid having to
resort to VBA. Hopefully, you can help me with the following...

I've used the Conditional Sum Wizard to set the value of a cell
depending on multiple conditions and that seems to work fine, but I'd
like to be able to use some dynamic, named ranges instead of absolute
cell references in my formula.

Here's the formula that works with the absolute references,

Code:
--------------------

{=SUM(IF(Payments!$A$2:Payments!$A$113=DATEVALUE( "06/04/2004"),IF(Payments!$A$2:Payments!$A$113<=DATEVALUE ("05/04/2005"),IF(Payments!$H$2:Payments!$H$113=$A6,Paymen ts!$E$2:Payments!$E$113,0),0),0))}
--------------------


What I'd really like to do is to replace the hard-coded reference to
row 113 as addtional rows are appended. I thought my best approach
would be to replace the cell range with a named range, as per the
following, but it didn't work.

Code:
--------------------

{=SUM(IF(payments_a=DATEVALUE("06/04/2004"),IF(payments_a<=DATEVALUE("05/04/2005"),IF(payments_h=$A6,payments_e,0),0),0))}
--------------------


Any suggestions?

Thanks :)


--
willee
------------------------------------------------------------------------
willee's Profile:
http://www.excelforum.com/member.php...o&userid=31189
View this thread: http://www.excelforum.com/showthread...hreadid=508589